Problem with SCOPE_IDENTITY - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Problem with SCOPE_IDENTITY

Author

Topic

gabba-labba
Starting Member

5 Posts

Posted - 2012-05-20 : 04:43:05

Hi @ All.

Got a little Problem with SCOPE_IDENTITY.

Following Code:

CREATE PROCEDURE CreateFoo
	@a NVARCHAR(100),
	@b NVARCHAR(100)
BEGIN
	INSERT INTO Foo (A, B)
	VALUES (@a, @b)

	DECLARE @fooID INT = SCOPE_IDENTITY()

	DECLARE @text NVARCHAR(250) = N'Foo Created'

	EXECUTE AddSystemNote @text

	SELECT @fooID
END

CREATE PROCEDURE AddSystemNote
	@text NVARCHAR(100)
BEGIN
	INSERT INTO SystemNotes (TEXT)
	VALUES (@text)
	
	SELECT SCOPE_IDENTITY()
END

Problem:

If i call CreateFoo, the returned result is NOT the SCOPE_IDENTITY of the inserted row in the Foo table, but the SCOPE_IDENTITY of the created SystemNote?

How do I get the correct SCOPE_IDENTITY?

Regards, Christian.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-20 : 06:02:49

DECLARE @fooID INT

INSERT INTO Foo (A, B)
VALUES (@a, @b)

set @fooid = SCOPE_IDENTITY()

N 56°04'39.26"
E 12°55'05.63"

gabba-labba
Starting Member

5 Posts

Posted - 2012-05-20 : 06:16:59

Nope, sorry.

Same problem.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-20 : 08:40:42

Works for me. Here is a complete set-up.

USE TempDB
GO
CREATE TABLE	dbo.Foo
		(
			FooID INT IDENTITY(1000, 1) PRIMARY KEY CLUSTERED,
			A NVARCHAR(100) NOT NULL,
			B NVARCHAR(100) NOT NULL
		)
GO
CREATE TABLE	dbo.SystemNotes
		(
			RowID INT IDENTITY(-1000, -1) PRIMARY KEY CLUSTERED,
			TextData NVARCHAR(100) NOT NULL
		)
GO
CREATE PROCEDURE	dbo.AddSystemNote
(
	@TextData NVARCHAR(100)
)
AS

INSERT	dbo.SystemNotes
	(
		TextData
	)
VALUES (
		@TextData
	)
	
SELECT	SCOPE_IDENTITY() AS [Should be -1000 from AddSystemNote procedure]
GO
CREATE PROCEDURE	dbo.CreateFoo
(
	@a NVARCHAR(100),
	@b NVARCHAR(100)
)
AS

INSERT INTO Foo (A, B)
VALUES (@a, @b)

DECLARE @FooID INT = SCOPE_IDENTITY()

DECLARE @TextData NVARCHAR(100) = N'Foo Created'

EXECUTE dbo.AddSystemNote @TextData

SELECT	@FooID AS [Should be 1000 from CreateFoo procedure]
GO
EXEC	dbo.CreateFoo N'Peter', N'Larsson'
GO
DROP TABLE	dbo.Foo,
		dbo.SystemNotes
GO
DROP PROCEDURE	dbo.CreateFoo,
		dbo.AddSystemNote

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-20 : 08:43:54

BTW, which version and edition of SQL Server are you using? I am not talking about the SSMS client version, but the SQL Service version you are running your code on.

There are rare cases when not even SCOPE_IDENTITY() works. If the unfortenate scenario with partitions and parallellism is happening on the same time (together with some other criteria), SCOPE_IDENTITY() will fail.

If this is the case, your only solution will be to rewrite your code to make use of the OUTPUT keyword.

N 56°04'39.26"
E 12°55'05.63"

gabba-labba
Starting Member

5 Posts

Posted - 2012-05-20 : 12:41:17

It works now.

I didn't know that the procedure CreateFoo returns two result sets (first the result set of AddSystemNote, then the result set of itself), so i read the fooid from the wrong result set.

Thanks for your help nonetheless.

Regards, Christian.

gabba-labba
Starting Member

5 Posts

Posted - 2012-05-20 : 12:50:17

Is it possible to Suppress AddSystemNote from returning a result set when called from another stored procedure?

Something like

EXECUTE AddSystemNote @text SEND RESULTS INTO @theVoid

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-20 : 12:58:42

quote:
Originally posted by gabba-labba

Is it possible to Suppress AddSystemNote from returning a result set when called from another stored procedure?

You can add a parameter to the procedure and the associated logic:

CREATE PROCEDURE AddSystemNote @param1 varchar(100), @no_output bit=0 AS
...procedure code
IF @no_output=0 BEGIN
...final SQL code that returns results
END

And call it like this: