CODEWITHSUNDEEP
sql-serversql-server-2005
pn.
pn.

Reputation: 872

How can SCOPE_IDENTITY return null when @@IDENTITY does not?

After executing an insert, I either select SCOPE_IDENTITY or @@IDENTITY.

SCOPE_IDENTITY returns null but @@IDENTITY does not.

I don't understand how this is possible.

Can you think of a reason why this happens?

Upvotes: 28

Views: 44033

Answers (5)

Mr. TA
Mr. TA

Reputation: 5359

In my case, both SCOPE_IDENTITY() and OUTPUT INTO didn't work because of INSTEAD OF INSERT trigger. The solution was to re-engineer the trigger to make it AFTER INSERT - luckily, I was able to do so in my case.

Upvotes: 0

JulesB
JulesB

Reputation: 111

SCOPE_IDENTITY will also return NULL when the insert is by sp_executesql as you are no longer in the scope of the INSERT!

Upvotes: 11

marc_s
marc_s

Reputation: 754338

KM hit the nail on the head:

  • @@IDENTITY gives you the last IDENTITY value inserted - no matter where in which table it was inserted (think triggers, e.g. into audit tables! Or even a cascade of triggers.....)

  • SCOPE_IDENTITY() gives you the last IDENTITY inserted in the scope of your statement, e.g. on the table(s) that your own, actual statement referenced (not those that might have been touched by a trigger)

Upvotes: 19

KM.
KM.

Reputation: 103589

here is one example of how SCOPE_IDENTITY() will be null but @@IDENTITY will have a value:

insert into a table with no identity, that table has an insert trigger that then inserts into a history table with an identity. SCOPE_IDENTITY() will be null (no identity in the local scope), but @@IDENTITY will report the identity from the trigger.

FYI, there is a known bug with SCOPE_IDENTITY(): https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=328811

Your best bet with identities is to use OUTPUT INTO, it can capture a set of IDs and is not subject to the SCOPE_IDENTITY() bug:

declare @x table (tableID int identity not null primary key, datavalue varchar(10))
declare @y table (tableID int, datavalue varchar(10))

INSERT INTO @x values ('aaaa')
INSERT INTO @x values ('bbbb')
INSERT INTO @x values ('cccc')
INSERT INTO @x values ('dddd')
INSERT INTO @x values ('eeee')


INSERT INTO @x
    (datavalue)
    OUTPUT INSERTED.tableID, INSERTED.datavalue     --<<<<OUTPUT INTO SYNTAX
    INTO @y                                         --<<<<OUTPUT INTO SYNTAX
SELECT
    'value='+CONVERT(varchar(5),dt.NewValue)
    FROM (SELECT id as NewValue from sysobjects where id<20) dt
    ORDER BY dt.NewValue


select * from @x
select * from @y

Upvotes: 23

Dustin Laine
Dustin Laine

Reputation: 38503

I found this on MSDN:

The SCOPE_IDENTITY() function will return the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.

You can read here: http://msdn.microsoft.com/en-us/library/ms190315.aspx

Your SQL code would be very helpful.

Upvotes: 3

Related Questions