Why Scope_Identity() returns null after inserting the first row in a table?

The tables have a parent-child relationship i.e. the first table has an identity column, and the second table references the first table. The issue is when isFirstMSH is false, the ProcessID value is NULL. If the isFirstMSH value is false, it should insert the last generated value in the table.

SQL server scope_identity

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.

If you call the procedure multiple times, those are different scopes, so SCOPE_IDENTITY() is expected to be null. And you need to be careful about 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:

CREATE TYPE dbo.TVPLabStagingDetails AS TABLE ( FirstName NVARCHAR(100), LastName NVARCHAR(100) );

Now the procedure becomes:

ALTER PROCEDURE dbo.[Insert_Header_Details_Tables] @FileName [varchar](50) = null, @VendorName [varchar](250) = null, @Names dbo.TVPLabStagingDetails READONLY AS BEGIN SET NOCOUNT ON; INSERT dbo.LabStagingHeader([FileName], [VendorName]) SELECT @FileName, @VendorName; DECLARE @ProcessID INT = SCOPE_IDENTITY(); INSERT dbo.LabStagingDetails([ProcessID], [LastName], [FirstName]) SELECT @ProcessID, LastName, FirstName FROM @Names; END

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. Some other tidbits:

• Always specify schema prefix
  
• Use semi-colons to terminate statements
  
• Wrap your procedure body with BEGIN/END and use SET NOCOUNT ON;
  
• Do not use IDENT_CURRENT()