Reminder - @@IDENTITY

@@identity reminder

This is old news to many people, but we just had a production issue at my work caused by @@identity so it probably doesn't hurt to remind folks.

You probably don't want to use @@IDENTITY if you're running SQL Server 2000.

@@IDENTITY returns the last autoincrement identity for any table in the current session, unlimited by scope. It's a global variable. That means that if your SP causes a trigger to fire and insert a row into another table with an autoincrement field, you'll get the second value. Remember that a DBA could potentially but a logging trigger on any of your tables without telling you, even if you don't use triggers.

SCOPE_IDENTITY() returns the last autoincrement identity for any table in the current session, limited to the current scope. That means you're safe from the trigger insert problem.

IDENT_CURRENT('table_name') is specific to the table, but not the session. That means you'll get the last ID inserted into the table, even if it was three weeks ago.

If you're closing your connection after running your stored procedure, you probably want SCOPE_IDENTITY().

More here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro01/html/sql01d4.asp