How to store values from a dynamic sql into a variable?

Question

I am trying to store next value for the sequence into a variable and the statement is called in a dynamic sql as below.

DECLARE @Sequence VARCHAR(100) = 'IMEIIDLookUP'
DECLARE @NextVal INT
DECLARE @SQL NVARCHAR(4000)

SELECT @SQL = 'SELECT (NEXT VALUE FOR [dbo].' + QUOTENAME(@Sequence) + ')'

SELECT  @NextVal = EXEC (@SQL)

SELECT @NextVal

The above query fails with error

Incorrect syntax near the keyword 'EXEC'.

What would be the correct syntax here? Having said that, I cannot avoid using dynamic sql.

read about sp_execute. It allows passing parameters back and forth — cha
– cha, Commented Aug 27, 2014 at 0:22

Gordon Linoff · Accepted Answer · 2014-08-27 00:34:11Z

5

Use sp_executesql:

DECLARE @Sequence VARCHAR(100) = 'IMEIIDLookUP';
DECLARE @NextVal INT;
DECLARE @SQL NVARCHAR(4000);

SELECT @SQL = 'SELECT @NextVal = (NEXT VALUE FOR [dbo].' + QUOTENAME(@Sequence) + ')';

exec sp_executesql @SQL, N'@NextVal int output', @NextVal = @NextVal output;

SELECT @NextVal;

answered Aug 27, 2014 at 0:34

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

How to store values from a dynamic sql into a variable?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related