CREATE PROCEDURE Employee_Insert (
@EmployeeId int OUTPUT
, @FirstName varchar(50)
, @LastName varchar(50)
, @DateOfBirth datetime
) AS
BEGIN
INSERT INTO Employee (
FirstName
, LastName
, DateOfBirth
)
VALUES (
@FirstName
, @LastName
, @DateOfBirth
)
SELECT @EmployeeId = @@IDENTITY
END
In this example, the Employee table's primary key is the EmployeeId, and it is also an identity column (meaning key values are generated by the database). After inserting the new Employee record into the table using the first statement, the second statement of the stored procedure saves the identity value that was used for the new record into the @EmployeeId output parameter, so that the data access layer can use it in some meaningful way. The way to find out the identity value that was used in the latest INSERT statement is simple - it's stored in the special @@IDENTITY variable, which is maintained by the database engine. This is very convenient, as it saves you from having to query the table again. But - is it really this simple? Does the @@IDENTITY variable always return the identity value from your last INSERT statement?
The answer is "not necessarily", in SQL Server 2005 at least. From the @@IDENTITY page in SQL Server 2005 Books Online:
"If the statement fires one or more triggers that perform inserts that generate identity values, calling @@IDENTITY immediately after the statement returns the last identity value generated by the triggers."
This means that if are using triggers on your Employee table which fire on insertion, and if those triggers in turn insert records into another table which also has an identity column, the result of the @@IDENTITY variable is the identity value used in the second insertion, and not the first, which is in most cases undesirable.
I came across this rather fun issue in an application I've been working on. From my data access layer, I was inserting several different records in a transaction, where one record had a foreign key relationship with another record's identity value. The way to save these new records correctly is to save the "parent" record first, then read the identity value returned from the stored procedure, update the corresponding field in the "child" record, and then insert the child record. I was getting unexpected results however, where my child records were not being created because of a violation of the foreign key constraint. After debugging, I found that following the insertion of the parent record, I was getting back identity values that didn't make sense - they were not even close to the previous identity values stored in the table.
On closer inspection of the database schema, I realised that the table I was inserting the parent record into had a trigger on it, who's purpose was to insert new records into a logging table which, sure enough, had an identity column in it. So when running the SELECT statement, the stored procedure was actually returning the identity value used in the logging table, which explains the violation of the foreign key constraint!
So now that we know what the issue is, how do we fix it? Once again, trusty SQL Server Books Online has the answer - use SCOPE_IDENTITY() instead of @@IDENTITY.
@@IDENTITY and SCOPE_IDENTITY return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope.
What this means essentially is that SCOPE_IDENTITY will always return the last identity value used in an INSERT statement that you called directly; INSERT statements nested inside triggers, or even other stored procedures which are called from the currently executing stored procedure, are considered to be in a different scope. Because of this, my advice would be to always use SCOPE_IDENTITY() when writing the "create" stored procedures for your CRUD operations, because 99% of the time, when you want the last used identity value, you're usually referring to the INSERT statement that you can actually see right in front of you.
So remember - Know your true @@IDENTITY! Hope this post saves someone out there a little pain. :)
- Tokes
