SQL: Scope Identity
Author: jason
Date: 2016-10-21 09:33:09
Category: Technical

I got caught by a feature that can be a bug if misused in SQL. The SCOPE_IDENTITY() function. According to MSDN SCOPE_IDENTITY() returns the last identity value that was inserted. But if you have an insert, then a no insert, you will get the first identity and not the null/non-existent identity.

Here is what I mean. I have an Insert that actually goes into the database and I grab the SCOPE_IDENTITY of 1. Then down in the code I have a second Insert. But the second insert code doesn't actually kick off. And when I grab the SCOPE_IDENTITY of that Insert (which doesn't exist) I get the first Identity of 1. The last identity value. *sigh* That's not what I wanted. I wanted NULL or nothing, because the second insert didn't actually insert. Well, if I wrap my SCOPE_IDENTITY() with a @@ROWCOUNT then I won't grab the wrong Identity value...

See this screengrab:



If you wrap the SET with a @@ROWCOUNT then you're good...

Code


DECLARE @First AS TABLE (Id INT NOT NULL IDENTITY(100,1), Field1 INT);
DECLARE @FirstID BIGINT = 0
DECLARE @Second AS TABLE (Id INT NOT NULL IDENTITY(1,1), Field1 INT);
DECLARE @SecondID BIGINT = 0

INSERT INTO @First
SELECT 0

IF @@ROWCOUNT > 0 BEGIN
SET @FirstID = SCOPE_IDENTITY();
END
SELECT @FirstID FirstID

INSERT INTO @Second
SELECT 0 WHERE 1 = 0 --Won't Insert a row

IF @@ROWCOUNT > 0 BEGIN
SET @SecondID = SCOPE_IDENTITY();
END
SELECT @SecondID SecondID



https://msdn.microsoft.com/en-us/library/ms190315.aspx




jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009