Menu
The MSDN docs weren't entirely clear on this one. or perhaps I'm not reading them well enough.
If I do an insert (which may insert zero rows), followed by
;SELECT SCOPE_IDENTITY()
And then call the command by ExecuteScalar()...
What will the result be if the Insert doesn't insert any rows?
I want to stop if it fails so that I don't continue on inserting child records to a bad, or wrong parent ID.
276
SCOPE_IDENTITY() returns the IDENTITY value inserted in T1. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function returns the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.
SCOPE_IDENTITY() : returns the last identity value generated in the current scope (i.e. stored procedure, trigger, function, etc). IDENT_CURRENT() : returns the last identity value for a specific table.
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.
@@Identity: It will return the last identity value generated in a connection, regardless of the table in which it was generated or the scope of the Insert statement that generated the identity. Scope_Identity(): It will return the last identity value generated in a connection, within the scope of the Insert query.
If no Identity is inserted SCOPE_IDENTITY() will return null, you can check for the condition you specify by assigning SCOPE_IDENTITY() to a variable and then checking the variables contents.
Illustration
Create Proc SomeInsertToFail(@ID int OUTPUT)
as
Begin
Select @ID = Scope_Identity()
End
Declare @SOMEID int
Exec SomeInsertToFail @SOMEID OUTPUT
Select @SOMEID --This will yield null
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
