Data Management

Account for scope when writing stored procedures

SQL Server provides the variable @@Identity to solve a particular problem that you may encounter when writing stored procedures. However, the @@Identity value has no concept of scope. Learn how you can work around this issue by using the new Scope_Identity() function.

A common problem in writing stored procedures arises when you need to insert one record into table T1 and one or more records into table T2, with the latter containing the PK of the row you just added to T1. Typically, this situation arises when you're doing batch inserts into a parent and child table.

To handle this situation, SQL Server provides the variable @@Identity. Following the insert into the parent table, you grab the value of its primary key from @@Identity.

This seems straightforward, but there is a subtle issue that could cause you problems. Before I show you how to work around the problem, let's look at the problem itself. Run the code in Listing A in Query Analyzer against some test database.

After creating the objects, you insert a row into the parent table, and save its key into a variable, and then another row into the child table. Now run these commands:

select * from identity_problem_a
select * from identity_problem_b

The results will look something like this:

1000    This is some text
1    1    This is still some text

The result is a bit surprising. Note the value returned in the second column from identity_problem_b. It's supposed to be 1000, the primary key that was just inserted into the identity_problem_a table. So why is the value 1 rather than 1000?

The reason is that the @@Identity value has no concept of scope in the sense that most programming languages do. Thus, even though the code presented grabs the Identity value immediately after performing the insert into the parent table, the trigger on the parent table performs another insert, into a different table with a different identity seed. So by the time you test @@Identity, its value has already changed and the old value is gone.

To address this problem, SQL Server 2000 adds the new Scope_Identity() function, which as its name implies, "remembers" the scope or context. Thus, the identity value inside the trigger is local to the trigger.

To fix the problem, we only need to change one line of the original code. You need to change this line:

SELECT @pk = @@IDENTITY

to this code:

SELECT @pk = Scope_Identity()

Now the code will perform as intended.

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!

Editor's Picks

Free Newsletters, In your Inbox