I have had a bad time debugging a stored procedure recently which apparently does not update foreign keys correctly on a few related tables. The part of the procedure where the problem occured looked like the following:
1. Insert values to a certain table
2. Assign @@identity to a local variable
3. Update another table with the value of Local Variable.
When a new record is inserted to a table, which has an identity column, the system global variable @@identity will hold the value of the newly inserted identity value. Most of the times it is helpful to inspect the @@identity variable to read the newly inserted value. So the code above looks correct. The people claimed that the process worked for a few years, and started updating incorrect values for the last few days.
While working with @@IDENTITY there are a few points to take care of. @@identity returns the last identity key inserted to any of the tables by the current database session. Let us take the following example:
1. Insert statement fires on Table A
2. Table A has a trigger which inserts a record to Table B
In the above case, when we inspect @@identity, it will point to the identity key generated for table B, not table A. This was the problem happening with the stored procedure I had been debugging. The developer added a new trigger to the table which inserted some values to a new table and hence the @@identity values that the procedure reads was not pointing to the identity value of the correct table.
How do we get the correct identity value? Well, there is another function which does that: SCOPE_IDENTITY(). Let us look at the following example.
1. Values are inserted to Table A
2. A new identity value is generated for table A (say 3440)
3. Trigger in Table A inserts a new record to Table B 3. A new identity value is generated for table B (say 6000)
At this stage: SCOPE_IDENTITY() will return 3440 and @@IDENTITY will return 6000
This essentially tells us that it is SCOPE_IDENTITY() that we need to use to read the value of the newly created identity values. (in the above case)
What do I do with my existing procedures?
I checked a few databases and found that there are many instances where @@identity is used. It is advisable to change the procedures to use SCOPE_IDENTITY(). (In this specific case) How do you know which all procedures are using @@IDENTITY variable? One option is to export the stored procedures to a .sql file and search for it. However, I created a query which can directly check in the system tables and return a list of stored procedures which uses @@identity variable in it. The following SQL statement returns a list of all procedures where @@identity is used.
SELECT DISTINCT o.name
FROM sysobjects o
INNER JOIN syscomments c ON (c.id = o.id)
WHERE xtype = 'P' and category = 0 and c.text LIKE '%@@identity%'
ORDER BY o.name
If you are creative, you can modify the above statement to search for all stored procedures in the system to search for specific values. Many a times I come across situations where I need to search for a specific string in all stored procedures.
Hope this helps
