Dynamic Sequence Numbers in SQL

DynamicSequenceNumbers.sql

-- Discussion on similar approach : https://stackoverflow.com/questions/2450695/sql-server-concurrency-and-generated-sequence

-- DROP TABLE [SequenceNumbers]

IF OBJECT_ID('SequenceNumbers', 'U') IS NULL BEGIN

    CREATE TABLE [SequenceNumbers]
    (
        [Name]                  SYSNAME     PRIMARY KEY,
        [Start]                 BIGINT      NOT NULL    DEFAULT 1,
        [Increment]             BIGINT      NOT NULL    DEFAULT 1,
        [Current]               BIGINT      NOT NULL,
        [CreatedTimestamp]      DATETIME2   NOT NULL,
        [UpdatedTimestamp]      DATETIME2   NULL,
    )

END;
GO

IF OBJECT_ID('spCreateSequence', 'P') IS NOT NULL BEGIN
    DROP PROCEDURE [spCreateSequence]
END;
GO

CREATE PROCEDURE[spCreateSequence]
(
    @Name                   SYSNAME,
    @Start                  BIGINT      = 0,
    @Increment              BIGINT      = 1,
    @FailIfExists           BIT         = 0
)

AS

DECLARE @ErrorText NVARCHAR(MAX);

BEGIN TRY
    INSERT [SequenceNumbers]
    (
        [Name],
        [Start],
        [Increment],
        [Current],
        [CreatedTimestamp]
    )
    OUTPUT  inserted.*
    VALUES
    (
        @Name,
        @Start,
        @Increment,
        @Start,
        SYSUTCDATETIME()
    )
    
END TRY
BEGIN CATCH
    IF (@FailIfExists = 1)
        THROW;
END CATCH

GO

IF OBJECT_ID('spGetSequenceNumber', 'P') IS NOT NULL BEGIN
    DROP PROCEDURE [spGetSequenceNumber]
END;
GO

CREATE PROCEDURE [spGetSequenceNumber]
(
    @Name                   SYSNAME,
    @Start                  BIGINT      = 0,
    @Increment              BIGINT      = 1,
    @FailIfDoesntExist      BIT         = 0
)

AS

DECLARE @ErrorText NVARCHAR(MAX);

-- OUTPUT TABLE
DECLARE @Generated TABLE
(
    [Name]                  SYSNAME     NOT NULL,
    [SequenceNumber]        BIGINT      NOT NULL
)

-- VALIDATE PARAMETERS
SET @Start                  = ISNULL(@Start, 1);
SET @Increment              = ISNULL(@Increment, 1);

IF @FailIfDoesntExist = 1 BEGIN
    IF NOT EXISTS(SELECT TOP 1 1 FROM [SequenceNumbers] WHERE [Name] = @Name) BEGIN
        SET @ErrorText = CONCAT('Sequence: ''', @Name, ''' does not exist');
        THROW 51000, @ErrorText, 1;
    END;
END;

BEGIN TRY
    -- ATTEMPT CREATE
    INSERT [SequenceNumbers]
    (
        [Name],
        [Start],
        [Increment],
        [Current],
        [CreatedTimestamp]
    )
    OUTPUT
        inserted.[Name],
        inserted.[Current]
    INTO @Generated
    VALUES
    (
        @Name,
        @Start,
        @Increment,
        @Start + @Increment,
        GETUTCDATE()
    )
END TRY
BEGIN CATCH
    -- UPDATE EXISTING TO NEXT NUMBER
    UPDATE  [SequenceNumbers]
    SET     [Current]           = ISNULL([Current], [Start]) + [Increment],
            [UpdatedTimestamp]  = GETUTCDATE()
    OUTPUT
            inserted.[Name],
            inserted.[Current]
    INTO    @Generated
    WHERE   [Name]      = @Name
END CATCH

-- RETURN
SELECT  [Name],      
        [SequenceNumber]
FROM    @Generated

GO

EXEC spCreateSequence 'Martin'

EXEC spGetSequenceNumber 'Bob'
EXEC spGetSequenceNumber 'Martin'
EXEC spGetSequenceNumber 'ABCDEF123'

select * from [SequenceNumbers]

BEGIN TRY
    EXEC spGetSequenceNumber 'Qwerty', @FailIfDoesntExist = 1
END TRY
BEGIN CATCH
    SELECT  
    ERROR_NUMBER() AS ErrorNumber  
    ,ERROR_SEVERITY() AS ErrorSeverity  
    ,ERROR_STATE() AS ErrorState  
    ,ERROR_PROCEDURE() AS ErrorProcedure  
    ,ERROR_LINE() AS ErrorLine  
    ,ERROR_MESSAGE() AS ErrorMessage;  
END CATCH