CODEWITHSUNDEEP
sqlsql-serversequence
invisibill
invisibill

Reputation: 51

SQL Server - get next value from sequence without advancing the current_value

I'm trying to write a stored procedure that verifies that the NEXT VALUE from a Sequence will be equal to the max number from a table, plus 1. I'm doing this by comparing the table number to the sequence's current_value from sys.sequences.

However, I am having trouble with this because I'm not sure how to differentiate between the case where the current_value is '1' and the NEXT VALUE will be '1', vs. the case where the NEXT VALUE will be '2'.

I do not want to actually call NEXT VALUE as i don't want to advance the count of the Sequence.

For example, this case:

CREATE SEQUENCE  testseq AS bigint START WITH 1 INCREMENT BY 1 CACHE 500000
select current_value from sys.sequences where name = 'testseq'

where current_value is 1 and next value will be 1

vs.

select NEXT VALUE for testseq
select current_value from sys.sequences where name = 'testseq'

where current_value is 1 and next value will be 2.

I will not be able to change the properties of the Sequence. Any ideas?

Upvotes: 5

Views: 11103

Answers (2)

sielnix
sielnix

Reputation: 73

Works for SQL Server 2017 and newer:

SELECT CASE WHEN last_used_value IS NULL
THEN CAST(current_value as bigint)
ELSE CAST(current_value as bigint) + 1
END AS next_val
FROM sys.sequences WHERE name = 'sequence_name'

Upvotes: 7

RoninEngineer
RoninEngineer

Reputation: 396

select cast(current_value as int) + cast(increment as int) from sys.sequences where name = 'testseq'

Upvotes: 4

Related Questions