Adding values to a sequence other than increment

• N_Muller

  SSCarpal Tunnel

  Points: 4037

  More actions

  February 26, 2016 at 10:58 am

  #308424

  I have my own sequence procedure that allows me to add a variable number of numbers to the sequence. For example:

  CREATE TABLE MySequence

  (

  SequenceName VARCHAR(50),

  LastValue BIGINT

  )

  where one of the rows in the table is:

  SequenceName = 'Sequence X'

  LastValue = 120

  Then I create a SQL stored procedure usp_MySequence where I pass the sequence name and an increment.

  EXEC @SequenceID = usp_MySequence ( @SequenceName, @Increment = NULL )

  where @Increment, when NULL it increments the sequence by 1. The returned value for @SequenceID is the last one of my from the @Increment value. For example:

  EXEC @SequenceID = usp_MySequence ( 'Sequence X', 1 ) -- returns 121

  EXEC @SequenceID = usp_MySequence ( 'Sequence X', NULL ) -- returns 122

  EXEC @SequenceID = usp_MySequence ( 'Sequence X', 10 ) -- returns 132

  I would like to change that and use the SQL Server Sequence, but I'm not sure I will have the flexibility of incrementing the sequence by 10 or any number other than the value assigned in the sequence definition with one call (preferred), or whether I need to call "NEXT VALUE FOR..." as many times as I need to increment by (cumbersome and probably a performance hog - not worth my change).

  Thanks

• Jon.Morisi

  SSChampion

  Points: 12846

  More actions

  February 26, 2016 at 1:36 pm

  #1861549

  So a dynamic increment value is what you're looking for?

  Maybe in-line this: ALTER SEQUENCE?