How to run to a sequence from stored procedure?

Question

I am trying to run a sequence from a stored procedure. I am passing the name of the sequence to the stored procedure and the stored procedure will return the sequence value, but the stored procedure is not recognizing the passed sequence name. The error says:

Incorrect syntax near '@SeqName'.

Here's what I have tried:

ALTER PROCEDURE [dbo].[GetSeqNextValue] 
   (@SeqName varchar(50), @NewNum bigint output) 
AS
BEGIN
    SET @NewNum = NEXT VALUE FOR @SeqName
END

You cannot pass a sequence name as a parameter in the same way you cannot pass a table name as a parameter to a query. you'll have to use dynamic sql. — ughai
– ughai, Commented Jun 29, 2015 at 10:15
Please add your use case for passing the sequence name as a variable. Perhaps there is better way, For a thorough discussion on dynamic SQL, see sommarskog.se/dynamic_sql.html. — Dan Guzman
– Dan Guzman, Commented Jun 29, 2015 at 10:20

Giorgi Nakeuri · Accepted Answer · 2015-06-29 10:21:13Z

4

You need some dynamic query here:

DECLARE @s NVARCHAR(100) =  'SET @NewNum = NEXT VALUE FOR ' + QUOTENAME(@SeqName)
EXEC sp_executesql @s, N'@NewNum bigint output', @NewNum OUTPUT

answered Jun 29, 2015 at 10:21

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

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

1 Comment

Giorgi Nakeuri Over a year ago

@Ala, hmm, did you went into a huddle? Wasn't this answer 5 min earlier then accepted?

Arun Gairola · Accepted Answer · 2015-06-29 10:26:20Z

3

you can alter your procedure as .

ALTER PROCEDURE [dbo].[GetSeqNextValue] (@SeqName varchar(50), @NewNum bigint output) 

AS

BEGIN
  Declare  @SQL  Nvarchar(1000)

  Set @SQL  = 'SELECT @NewNum = Next Value for ' + @SeqName 
  Exec sp_executesql @Sql,N'@NewNum bigint output',@NewNum output 


END

answered Jun 29, 2015 at 10:26

Arun Gairola

8944 silver badges14 bronze badges

Collectives™ on Stack Overflow

How to run to a sequence from stored procedure?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related