sql - T-SQL 存储过程从计数器多次获取相同的值

Question

我创建了一个存储过程，该过程应该通过每次调用时增加表中的值来返回唯一的数字。

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

有时存储过程会返回相同的数字两次。我做错了什么？

Answer 1

我以前这样做过:

update CounterTable set @Next_Number = Counter = Counter + 1

将其保留在单个语句中，因此(除非您可能切换到“未提交的读取”)不应该对多个调用者产生问题。