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:
ALTER PROCEDURE [dbo].[Insert_Header_Details_Tables]
(
@ProcessID [int]=null,
@FileName [varchar](50)=null,
@VendorName [varchar](250)=null,
@LastName [nvarchar](100)=null,
@FirstName [nvarchar](100)=null,
@isFirstMSH [bit]
)
AS
--BEGIN TRANSACTION
-- Insert into Jobs table
IF(@isFirstMSH = 1)
BEGIN
INSERT INTO LabStagingHeader
([FileName],
[VendorName]
)
VALUES (@FileName,
@VendorName
)
END
-- Retrieve the automatically @ProcID VALUE from the Header table
SET @ProcessID = SCOPE_IDENTITY()
-- Insert new values into LabStagingDetails table
INSERT INTO LabStagingDetails
([ProcessID],
[LastName],
[FirstName]
)
VALUES (@ProcessID,
@LastName,
@FirstName
)
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 table.