nigel. (4/19/2010)
/* lots of quoting here */
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.
BOL on SCOPE_IDENTITY:
Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.
In the example, since there are no GO statements to define batch boundaries, aren't all the statements in the same batch?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP