SQL Server Scope_Identity() returns null after inserting the first row in a table instead of the last generated value. How to Fix it?
I have a stored procedure that is used for inserting values in two tables. The tables have a parent-child relationship i.e. the first table has an identity column, and the second table references the first table. In the following procedure, I am returning Scope_Identity() value as a column:
The issue is when isFirstMSH is false, the ProcessID value is NULL. If isFirstMSH value is false, it should insert the last generated value in the table.
If you call the procedure multiple times, those are different scopes, so the SQL Server SCOPE_IDENTITY() is expected to be null. Be clear and careful about the concepts - if you call the procedure multiple times, how is the second invocation really going to be sure that "the last generated value" was from the previous invocation from that process, vs. some other concurrent call to the same procedure? Consider table-valued parameters so that you only have to call the stored procedure once, and you can stop tracking if this call is inserting the "first" row and worrying about multiple users colliding:
Now the procedure becomes:If that kind of change is not possible, push back; if that also fails, then you'll need to use an output parameter so that the client-side can pass in @ProcessID on subsequent calls. But really that is the least efficient way to do this. What is SQL Server Scope_Identity? SQL Server 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, if two statements are in the same stored procedure, function, or batch, they are in the same scope.