Posted by Erland Sommarskog on 04/13/06 10:18

DickChristoph (dchristo99@yahoo.com) writes:

>> Then again, there are cases where @@identity will give you the correct
>> result, and scope_identity() will not.
>
> Could you give an example of when this might occur?

CREATE TABLE #xyz(a int IDENTITY, b int NOT NULL)
go
EXEC sp_executesql N'INSERT #xyz(b) VALUES(@b)', N'@b int', 12
SELECT scope_identity(), @@identity
do
DROP TABLE #xyz

While the example may look contrived, many client API uses sp_executesql
or similar under the hood. scope_identity() returns the latest generated
identity value in the current scope, so if you call back a second time
from the client to get the value, you can only hope the both commands
excecuted in the top scope of the connection.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]