Microsoft KB Archive/139620

{|
 * width="100%"|

BUG: @@IDENTITY in Nested Stored Procedure Returns NULL

 * }

Q139620

-

The information in this article applies to:


 * Microsoft SQL Server version 6.0

-

BUG# NT: 11897 (6.00)

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.

WORKAROUND
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 Additional query words: sql6 windows nt sproc sp

Keywords : kbprogramming

Issue type :

Technology : kbSQLServSearch kbAudDeveloper kbSQLServ600