CODEWITHSUNDEEP
sql-server
Stephen
Stephen

Reputation: 23

SQL Server stored procedure inserts second row when it was supposed to read what it just inserted when called with the same parameter

I have a SQL Server stored procedure that is executed by passing CustNum and is supposed to return Cust_Key. If the CustNum exists, then it should return the existing Cust_Key, else it should insert the CustNum into the table and returns the newly created Cust_Key.

In the table, Cust_Key_Initial is an identity and Cust_Key takes the value of Cust_Key_Initial initially. Table is designed like so because Cust_Key could change but Cust_Key_Initial will not change.

Now the issue I am having is, once is a while, the stored procedure inserts a CustNum to the table twice in a split second difference instead of reading Cust_Key for the CustNum that was just inserted. I don't understand how this happens, please help me with this. The definition of the procedure is below. Also there is no speed issue here, it usually returns in about 100 milliseconds.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE usp_GetCustKey
    @CustNum Varchar(10) NULL
AS
    SET NOCOUNT ON;
BEGIN
    IF NOT EXISTS (SELECT 1 FROM dbo.value_Customer WITH (NOLOCK) 
                   WHERE CustNum= @CustNum)
    BEGIN 
        INSERT INTO dbo.value_Customer (Cust_Key, CustNum, CustNum_Initial)
            SELECT -1, @CustNum, @CustNum

        UPDATE dbo.value_Customer 
        SET Cust_Key = Cust_Key_Initial
        WHERE Cust_Key_Initial = SCOPE_IDENTITY()
    END   

    -- Return Value
    SELECT TOP 1 Cust_Key 
    FROM dbo.value_Customer WITH (NOLOCK)
    WHERE CustNum = @CustNum
END

Upvotes: 1

Views: 85

Answers (2)

SteveC
SteveC

Reputation: 6015

This is (imo) a good approach for avoiding race conditions, which it seems is the issue you're having here. The procedure first attempts to find the Cust_Key_Initial. If it's found, i.e. @@rowcount!=0, then it's returned. If it's not found then it's created and used to update the table to set the Cust_Key to equal the Cust_Key_Initial.

ALTER PROCEDURE usp_GetPersonKey
    @CustNum Varchar(10) NULL
AS
set nocount on;
declare
  @Cust_Key_Initial     int;

select @Cust_Key_Initial=Cust_Key 
from dbo.value_Customer /*WITH (NOLOCK)*/
where CustNum = @CustNum;

if @@rowcount=0
    begin
        insert into dbo.value_Customer (Cust_Key, CustNum, CustNum_Initial)
            select -1, @CustNum, @CustNum
        select @Cust_Key_Initial=scope_identity();
        
        update dbo.value_Customer 
        set Cust_Key=Cust_Key_Initial
        where Cust_Key_Initial = @Cust_Key_Initial;
    end   

select @Cust_Key_Initial;
go

Upvotes: 1

Amirhossein Borghei
Amirhossein Borghei

Reputation: 26

It seems to me that Cust_key_Initial is not an identity specified column because normally you cannot insert a row determining its identity so SCOPE_IDENTITY returns you either NULL or the value identity on your table which is different from @CustNum

Upvotes: 0

Related Questions