SQL Sequence is skipping values when used in a case statement

Question

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!

You're creating a sequence object in the database based on the current max value in a table? This seems like a lot of extra scaffolding for nothing, as well as a concurrency killer (not only because you have to drop the sequence before anyone else can run the code, but also because you don't have sufficient semantics to protect you from someone else inserting another Id between your SELECT (MAX+1) and your eventual insert). — Aaron Bertrand
– Aaron Bertrand, Commented Aug 16, 2022 at 3:57
Aaron raises a valid point. Can provide a little more information about your process? Is it a data Import/processing job? Do you use RBAR approach e.g. cursor or do you use set based logic? — Alex
– Alex, Commented Aug 16, 2022 at 4:05
For set-based logic you can look at something like this. If you're using a cursor to insert a single row at a time, well, just only increment the sequence when the date isn't NULL, like this. But I still think a sequence is overkill in that case - just increment your own counter, which doesn't require a serializable object for use by a single session, and doesn't require dynamic SQL. — Aaron Bertrand
– Aaron Bertrand, Commented Aug 16, 2022 at 4:19
@BenThul this site isn't just about solving OP's problem, its also about best practice which includes ensuring OP is using (and understands) the correct terminology. And (not in this situation), but many times when people are asking a question involving CASE it is the very fact that they use it as a statement which has tripped them up. — Dale K
– Dale K, Commented Aug 16, 2022 at 19:56

Md. Suman Kabir · Accepted Answer · 2022-08-16 04:28:25Z

1

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

edited Aug 16, 2022 at 4:28

answered Aug 16, 2022 at 3:51

Md. Suman Kabir

5,4615 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alex Over a year ago

"Msg 11741, Level 15, State 1, Line 19 NEXT VALUE FOR function cannot be used within CASE, CHOOSE, COALESCE, IIF, ISNULL and NULLIF. "

Md. Suman Kabir Over a year ago

@Alex Corrected

Collectives™ on Stack Overflow

SQL Sequence is skipping values when used in a case statement

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related