T-Sql @@Identity

Question

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

Answer 1

Like this:

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

Answer 2

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)

Answer 3

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).

Answer 4

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)

Answer 5

You can use this

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

Where Id is your Identity field