CODEWITHSUNDEEP
t-sqlidentity
Itay.B
Itay.B

Reputation: 4111

T-Sql @@Identity

Does someone know how can I return the @@Identity when using T-Sql?

Something like this:

set @Sql = "insert into table....values()..."
exec @sql
return @@Identity

Upvotes: 5

Views: 1730

Answers (5)

Joe White
Joe White

Reputation: 97676

INSERT INTO TableName (Field1, Field2, Field3) VALUES (1, 2, 3);
SELECT SCOPE_IDENTITY();

This is a multi-statement batch, so I'm not sure that every client library will return values the same way; in classic ADO, for example, it's possible that you might need to advance to the next recordset before you can read the value. But if you're using ADO.NET, I know that you can just use ExecuteScalar on the whole string above, and it will return your SCOPE_IDENTITY value just fine.

Caution: ADO.NET will return the value as a decimal, not an int like you might expect. This is because SCOPE_IDENTITY, for whatever reason, is typed as numeric(38,0). So you either need to cast the ExecuteScalar result to decimal before you cast it to int, or you need to SELECT CAST(SCOPE_IDENTITY() AS INT) (assuming your IDENTITY field is an INT, and not some larger numeric type).

Upvotes: 3

Azhar
Azhar

Reputation: 20670

You can use this

Insert into Table(Col2, Col3) 
output inserted.Id
values ('xyz', 'abc')

Where Id is your Identity field

Upvotes: 1

Ken
Ken

Reputation: 311

It looks like one of your implicit requirements is the execution of dynamic SQL. While I'd advise against this, you can accomplish what you're looking for with this:

set @Sql = 'insert into table....values()...; select SCOPE_IDENTITY()'
exec(@Sql)

Upvotes: 6

EventHorizon
EventHorizon

Reputation: 2986

Append ";select @@identity" to your insert statement:

insert into tab (x,y,z) values (a,b,c); select @@identity

The returned value is the ID (use ExecuteScalar)

Upvotes: 3

SLaks
SLaks

Reputation: 887285

Like this:

INSERT INTO Table(...)
OUTPUT INSERTED.IdColumn
VALUES(...)

Upvotes: 4

Related Questions