3

Given the following code:

CREATE SEQUENCE dbo.NextTestId AS [bigint]
 START WITH 10 INCREMENT BY 2 NO CACHE 
GO

DECLARE  
  @variableNumberOfIdsNeeded INT = 7, -- This will change for each call
  @FirstSeqNum SQL_VARIANT , @LastSeqNum sql_variant, @SeqIncr sql_variant;  

EXEC sys.sp_sequence_get_range @sequence_name = N'dbo.NextTestId', 
            @range_size = @variableNumberOfIdsNeeded, 
            @range_first_value = @FirstSeqNum OUTPUT, 
            @range_last_value = @LastSeqNum OUTPUT, 
            @sequence_increment = @SeqIncr OUTPUT;

-- The following statement returns the output values
SELECT @FirstSeqNum AS FirstVal, @LastSeqNum AS LastVal, @SeqIncr AS SeqIncrement;

I get a result like this:

FirstVal    LastVal    SeqIncrement 
-------     -------    -------------- 
38          50         2

I would like to have a set based way to take these results and insert each value (skipping by SeqIncrement) into this table:

DECLARE @newIds TABLE (IdType VARCHAR(100), [NewId] BIGINT)

So when I am done, a SELECT * from @newIds would return:

IdType    NewId
-------   -------
TestId    38
TestId    40
TestId    42
TestId    44
TestId    46
TestId    48
TestId    50

NOTE: I don't want to use a loop if possible. Also, I will need to get a variable amount of results (this one shows 7, but it will change each call).

I think there may be a cross apply or some such thing that can do this. But I can't seem to figure it.

  • Do you mean you want a set-based way that avoids the call to sp_sequence_get_range, or in addition to? – Aaron Bertrand Jun 29 '15 at 16:44
  • @AaronBertrand - I mean that after the call to sp_sequence_get_range, I want a set based way to insert 38 - 50 (every other) into a table. So no, I don't want to avoid the call to sp_sequence_get_range. (I updated my question to be a bit more clear on this.) – Vaccano Jun 29 '15 at 16:47
5

This should be good up to about 2,500 values (depending on version):

;WITH x(n) AS 
(
  SELECT TOP (@variableNumberOfIdsNeeded)
    (ROW_NUMBER() OVER (ORDER BY number)-1) 
    * CONVERT(BIGINT, @SeqIncr)
    + CONVERT(BIGINT, @FirstSeqNum)
  FROM master.dbo.spt_values
  ORDER BY number
)
--INSERT @newIds([NewId]) 
SELECT n FROM x;

If you need more, or are worried about using the view master.dbo.spt_values, see other possible techniques for generating sets without looping here:

  • Perfect! Thank you! What is master.dbo.spt_values? Looks like a system table. Are you just using it because it has a lot of rows? Is it safe to use a system table like this? – Vaccano Jun 29 '15 at 16:52
  • Yes, it's a system table (well, technically it's a view), should be relatively safe, but you can instead use things like sys.all_objects in your local database. Or create your own numbers table, as I advocate in those articles. – Aaron Bertrand Jun 29 '15 at 16:54
  • I like the number table option. Thank you again for the great answer! – Vaccano Jun 29 '15 at 16:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.