There are several ways to handle this, and the SCOPE_IDENTITY approach is certainly tried and true.

INSERT INTO MyTable (ColumnList)
OUTPUT Inserted.*
VALUES (ValueList)

And the insert statement will give you a snapshot of the row after the insert finishes. This can be very helping in getting the state of the row after the insert, and potentially eliminates the need to make a round trip back to the server to query the new row from the value returned by scope_identity.


This is what I used (or tested) last night. I placed

Set @NewPk = SCOPE_IDENTITY()

right after the INSERT where I am looking for an Identity value. And it worked.
Thank you.

>Use SCOPE_IDENTITY()
>
>Alternative way would be to use OUTPUT clause and you can even write a single insert command to insert in both tables at once (composable DML).
>
>>Hi,
>>
>>I am sure I have done it before but forgot how to do it. In a Stored Procedure, the code Inserts a row into a table.
>>How do I get the PK (Identity) of the newly added row?
>>Also, is it after COMMIT or before?
>>
>>TIA
>>
>>UPDATE. I code I found is as follows (right after INSERT)
>>Set @NewPk = SCOPE_IDENTITY()
>>However, the stored procedure does two tables INSERT. If I do the SCOPE_IDENTITY() right after the table where I need the Identity, will it work?
>>Google shows two more approaches:
>>@@Identity.
>>Ident_Current()
>>But which one to choose?