Using @@identity in a stored procedure good/bad?

I have a stored procedure that inserts a record which has Identity column. Immediately after inserting I am using @@identity to insert a records in child table.

Are there any implications d开发者_运维知识库oing that ?

It's usually not as good as SCOPE_IDENTITY, if your version offers this, because @@Identity isn't limited to the current scope. It will retrieve the most recent identity even if it was from a different sp in a different table.

Pinal Dave has a straightforward explanation of the IDENTITY offerings here: http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

SCOPE_IDENTITY should be used. If the INSERT should fire a trigger that also performs an identity insert, you'll get the wrong value (i.e., the value generated by the trigger's insert) from @@identity.

@@IDENTITY is the last identity value inserted for ANY record. If you get high user concurrency, you're going to end up with the wrong identity value, i.e., you'll get a value which another request just inserted.

For the last identity value inserted in the current scope, use SCOPE_IDENTITY.

Just to add my favoriate artilce on this if for nothing else then for its title "Identity Crisis"