- SCOPE_IDENTITY() returns the value of the Identity column for the last insert, for the current session (i.e. sproc calling SCOPE_IDENTITY()) AND current scope (i.e. trigger inserts caused by your insert are not considered)
- @@IDENTITY returns the value of the Identity column for the last insert, for the current session but REGARDLESS of scope (i.e. trigger inserts caused by your insert are considered)
- IDENT_CURRENT('tablename') returns the value of the Identity for the last insert, REGARDLESS of session or scope
so in general, you probably want to be using SCOPE_IDENTITY() to get the last inserted value for an Identity column. if there are no triggers on the table being inserted to, @@IDENTITY and SCOPE_IDENTITY() are identical, but if a trigger is later added your sproc will need to be changed. you almost never want to use IDENT_CURRENT(‘tablename’), since the current value can easily change between the time you make your call to this function and you do something with the result--
