4

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.

0

2 Answers 2

9

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:

1

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions because they return values that are inserted into identity columns. if the table has no defined identity field SCOPE_IDENTITY returns NULL

BEGIN TRANSACTION;
    CREATE TABLE dbo.sample (id          INT
                         PRIMARY KEY IDENTITY(1, 1), 
                         description VARCHAR(20)
                        );
    INSERT INTO dbo.sample
    (
        --id - column value is auto-generated
        description
    )
    VALUES
    (
        -- id - int
        'sample reg' -- description - varchar
    );
    SELECT SCOPE_IDENTITY(), 
           'return SCOPE_IDENTITY';
ROLLBACK TRANSACTION;
GO
BEGIN TRANSACTION;
    CREATE TABLE dbo.sample
                    (id          INT
                     PRIMARY KEY, 
                     description VARCHAR(20)
                    );
    INSERT INTO dbo.sample
    (id, -- column value is auto-generated 
     description
    )
    VALUES
    (20
     ,-- id - int 
     'sample reg' -- description - varchar
    );
    SELECT SCOPE_IDENTITY(), 
           'null return SCOPE_IDENTITY';
ROLLBACK TRANSACTION;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.