BUG: @@IDENTITY in Nested Stored Procedure Returns NULL

• Microsoft SQL Server, version 6.0

SYMPTOMS

The @@identity global variable will be NULL under the following conditions:

1. If it is queried in a stored procedure after a nested stored procedure call and insert.

2. The nested stored procedure performs an insert and queries the @@identity global variable as well.

Do not query the @@identity global variable from more than one stored procedure nesting level.

For example, modify the "add_test1" stored procedure (from example in the following MORE INFORMATION section) to the following code and it works properly:

   CREATE PROCEDURE add_test1 AS
   EXECUTE add_test2
   EXECUTE add_test2
   RETURN
   GO

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.0. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

The following script shows the @@identity global variable returning a NULL value:

   CREATE TABLE test1
   (
   id int IDENTITY(1,1) NOT NULL ,
   name char (20) NOT NULL
   )
   GO
   CREATE PROCEDURE add_test2 AS
   INSERT test1 VALUES ('1')
   SELECT @@identity
   RETURN
   GO
   CREATE PROCEDURE add_test1 AS
   EXECUTE add_test2
   INSERT test1 VALUES ('2')
   SELECT @@identity
   RETURN
   GO
   EXECUTE add_test1
   GO