CODEWITHSUNDEEP
t-sqlsql-server-2008
TonyP
TonyP

Reputation: 5873

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 doing that ?

Upvotes: 0

Views: 692

Answers (4)

Conrad Frix
Conrad Frix

Reputation: 52645

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

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135809

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.

Upvotes: 3

Chris B. Behrens
Chris B. Behrens

Reputation: 6295

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

Upvotes: 1

Matthew
Matthew

Reputation: 10444

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/

Upvotes: 4

Related Questions