CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
RobinHu
RobinHu

Reputation: 384

T-SQL stored procedure getting same value several times from counter

I have created a stored procedure that is supposed to return a unique number by incrementing a value in a table every time it is called.

ALTER PROCEDURE [dbo].[GetNextNumber] @Next_Number char(9) output AS
BEGIN
    BEGIN TRAN

    DECLARE @Current_Number int
    SET @Current_Number = (SELECT CONVERT(int, Counter) FROM CounterTable)

    IF(@Current_Number IS NULL)
    BEGIN
        SET @Next_Number = '000000000'
        INSERT INTO CounterTable (Counter) VALUES (@Next_Number)
    END
    ELSE IF(@Current_Number >= 999999999)
    BEGIN
         SET @Next_Number = '000000000'
         UPDATE dbo.CounterTable SET Counter = @Next_Number
    END
    ELSE
    BEGIN
        SET @Next_Number = REPLACE(STR(@Current_Number + 1, 9),' ', '0')
        UPDATE dbo.CounterTable SET Counter = @Next_Number
    END

    COMMIT TRAN
END

Sometimes the stored procedure returns the same number twice. What have I done wrong?

Upvotes: 4

Views: 442

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239654

I've done this before:

update CounterTable set @Next_Number = Counter = Counter + 1

which keeps it in a single statement, and so (unless you switch to Read Uncommitted, possibly) shouldn't have issues with multiple callers.

Upvotes: 3

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

You could explicitly lock the Countertable using Table Hints

...
SET @Current_Number = (
   SELECT CONVERT(int, Counter) 
   FROM CounterTable WITH(Holdlock, Updlock)
)
...

Upvotes: 2

Related Questions