SQL Server Manual Sequence Numbers

SequenceNumbers.sql

-- DROP TABLE [SequenceNumbers]

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

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

END;
GO

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

CREATE PROCEDURE [spGetSequenceNumber]
(
    @Name                   SYSNAME,
    @Start                  INT         = 1,
    @Increment              INT         = 1
)

AS

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

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

BEGIN TRY
    -- ATTEMPT CREATE
    INSERT [SequenceNumbers]
    (
        [Name],
        [Start],
        [Increment],
        [Current],
        [CreatedTimestamp]
    )
    OUTPUT
        inserted.[Name],
        inserted.[Current]
    INTO @Generated
    VALUES
    (
        @Name,
        @Start,
        @Increment,
        @Start,
        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 spGetSequenceNumber 'Qwerty'
exec spGetSequenceNumber 'Bob'

select * from [SequenceNumbers]