Wednesday, August 09, 2006

 

Always Use SCOPE_IDENTITY() in Stored Procedures

Now I’m sure most developers and DBAs are aware of this, but I mention it because it crops up every once in a while in production code. There are 3 ways to retrieve the last generated identity value for table: @@IDENTITY, IDENT_CURRENT(‘tablename’) and SCOPE_IDENTITY().

If you want to make sure that retrieved identity value is correct, use only SCOPE_IDENTITY().
In fact, it’s worth doing a search for the other 2 possibilities across all your projects’ stored procedures just to be sure.

From Books Online:

  • SCOPE_IDENTITY() returns the last identity value generated for any table in the current session and the current scope.
  • IDENT_CURRENT(‘tablename) returns the last identity value generated for a specific table in any session and any scope.
  • @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.

# posted by Mitch Wheat @ 9:04 am

0 Comments:

Post a Comment



<< Home
    

Powered by Blogger