SQL Sequence is skipping values when used in a case statement

user_11478380 0
Viewed 79

Using SQL Server 2016, I'm trying to use a sequence to insert values automatically into a column, when another value is not null. However, if the case value is null, the next non-null NEXT VALUE FOR has increased by however many null values precede it. I'm unsure how to prevent this from occurring. The code is below:

The relevant part of the stored proc to create the sequence:

DECLARE @IdMax INT;
SELECT @IdMax = (MAX(CAST(Id AS Int)) + 1) from [db].[dbo].[table] WHERE ISNUMERIC(Id) = 1;

EXEC('CREATE SEQUENCE Id_Sequence
START WITH ' + @IdMax + '
INCREMENT BY 1
CACHE 1000;')

And the rough testing implementation:

DECLARE @StartDate date
SET @StartDate = '20220816'

DECLARE @IDSequenceValue int
SET @IDSequenceValue = CAST(NEXT VALUE FOR Id_Sequence as nvarchar(7))

INSERT INTO dbo.table ( 
StartDate, 
Id) 
Values (
@StartDate,
CASE
    WHEN @StartDate IS NULL THEN NULL
    ELSE @IdSequenceValue
END);

The code has been abridged to remove identifiable information. I had to declare the NEXT VALUE FOR statement as a variable to get around not being able to use it within the Case statement directly. My abridged outputs are:

StartDate   Id
2022-08-17  5078561
NULL        NULL
NULL        NULL
2022-08-17  5078558
2022-08-17  5078557
NULL        NULL
2022-08-17  5078555
2022-08-17  5078554
2022-08-17  5078553

Can this issue be overcome? I'm at a loss here. Thanks!

sequence sql-server sql
1 Answers

The issue is, you are generating the next sequence number always by this statement :

SET @IDSequenceValue = CAST(NEXT VALUE FOR Id_Sequence as nvarchar(7))

In your CASE expression, you are using the value conditionally. To resolve the issue, I would suggest to generate the next sequence number only if it is required to use like this :

if @StartDate IS NOT NULL
    begin
        SET @IDSequenceValue = CAST(NEXT VALUE FOR Id_Sequence as nvarchar(7))
    end
user_56959530
Related