Reset SCOPE_IDENTITY()

Question

I have a stored procedure that first inserts some data into a temp table and then inserts a row into another table. I am calling Scope_Identity() after the second insert to pick up the newly inserted record Identity.

If the second insert does nothing due to a join, I want to check the Scope_Identity and raise an exception. But Scope_Identity is returning the last identity created from the temp table insert before the second insert.

Is there a way to reset SCOPE_IDENTITY before calling the second insert, or a better way to determine if the second insert didn't actually insert anything?

Answer 1

Martin Smith's answer totally answers your question.

This is apparently the only page on the internet asking how to reset the Scope_Identity().
I believe this is vital for anyone working with T-SQL.

I am leaving this answer for anyone who came here (like me) looking for the identity that was inserted by the previous insert statement (and not the last randomly successful identity insert).

This is what I came up with:

SET @SomeID = (CASE WHEN @@ROWCOUNT > 0 THEN SCOPE_IDENTITY() ELSE NULL END)

Answer 2

Check @@ROWCOUNT immediately after the 2nd insert. If it is 0 then no rows were inserted.

INSERT INTO YourTable
SELECT ...

IF (@@ROWCOUNT = 0)
BEGIN
RAISERROR('Nothing inserted',16,1)
RETURN
END