What happens if I Select SCOPE_IDENTITY() after an insert fails (SQL Server 2005)

Question

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.

Answer 1

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