Tuesday, 27 July, 2004

This is an important little tit-bit. In Sql Server, you often want to add a record then return the identity value assigned to the record you've just added. The way you see this done on pretty much every SQL server site is something like the following:

create procedure InsertRecord
@ID int out,
@Name varchar(30),
@Email varchar(30),
@PostCode varchar(6),
as Insert into tblPerson (Name,Email,Postcode) Values (@Name,@Email, @Postcode);
Select @ID=@@identity;

The problem with this @@identity approach is that the scope is too wide. The value of @@identity is the last identity value generated for an insert for your session. If some trigger is fired after you insert a record and that trigger itself creates a record then the @@identity value will return the identity that belongs to the trigger's insert and not the parent table's insert that caused the trigger in the first place.

What you actually want is a function called scope_identity(). It contains the last identity value used for an insert in your current stored procedure - it doesn't give a damn about what else is going on in the session.

See these two articles for a bit more detail:
[1] - MSDN
[2] - Database Journal

22:51:21 GMT | #Programming | Permalink
XML View Previous Posts