da-zero (4/19/2010)
nigel. (4/19/2010)
Andrew Watson-478275 (4/19/2010)
A more interesting result is from this:
DECLARE @sid int
DECLARE @table Table (KeyID int primary key,KeyData varchar(5))
DECLARE @idtable Table (KeyID int identity primary key,KeyData varchar(5))
INSERT INTO @idtable (KeyData) VALUES('Test1')
INSERT INTO @table (KeyID,KeyData) VALUES(1,'Test1')
INSERT INTO @table (KeyID,KeyData) VALUES(2,'Test2')
SET @sid = SCOPE_IDENTITY()
select @sid
This still returns NULL, even though there has been an identity insert and the latest operations were on tables without identities.
... If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. ...
But in the example there is a statement that affects a table with an identity constraint, so SCOPE_IDENTITY (or @@IDENTITY ) should have a value different from NULL, right?
I don't think so. The SCOPE_IDENTITY (or @@IDENTITY) function will return the value appropriate to the most recent INSERT statement whether that statement affects a table with an IDENTITY column or not
Maybe I should have included the preceding sentence from BOL which would have put it into context.
After an INSERT, SELECT INTO, or bulk copy statement is completed, @@IDENTITY contains the last identity value that is generated by the statement. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL.