Multiple Sequence Values

Question

Post reply

Multiple Sequence Values

Steve Jones - SSC Editor

SSC Guru

Points: 742255
More actions
April 27, 2026 at 12:00 am

#4767694

Comments posted to this topic are about the item Multiple Sequence Values

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80224 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Uwe Ricken Hall of Fame Points: 3262 More actions · Answer 2

Hey Steve,

maybe the question is a little bit misleading. Do you want to have the next 12 values? (1, 2, 3, 4, ...) or do you want to have the first and the last sequence value (1, 12)?

/* List of the next 12 values */
CREATE SEQUENCE dbo.order_num_seq
AS BIGINT
START WITH 1
INCREMENT BY 1
MINVALUE 1;

SELECT NEXT VALUE FOR dbo.order_num_seq AS SeqValue
FROM (VALUES
    (1),(2),(3),(4),(5),(6),
    (7),(8),(9),(10),(11),(12)
) AS t(n);

/* Start and End of sequence range */
DECLARE 
    @first sql_variant,
    @last sql_variant;

EXEC sys.sp_sequence_get_range
      @sequence_name = N'dbo.order_num_seq',
      @range_size = 12,
      @range_first_value = @first OUTPUT,
      @range_last_value = @last OUTPUT;

SELECT @first AS FirstValue, @last AS LastValue;

Microsoft Certified Master: SQL Server 2008
MVP - Data Platform (2013 - ...)
my blog: http://www.sqlmaster.de (german only!)

Steve Jones - SSC Editor SSC Guru Points: 742255 More actions · Answer 3

I'm not sure getting the next 12 values is misleading. It's not any particular range. If my current value in a table is 12, then I want 13-24. If it's 100, then 101-113. Happy to have a debate, but I think relatively few people use, or understand, sequence values, so I was trying to surface that NEXT VALUE doesn't take a parameter, and there is a proc to do this. I'll amend the question as I should note the first loop answer works, though not great. I'll say run this quickest.