CODEWITHSUNDEEP
sqlsql-server
user1144852
user1144852

Reputation: 265

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

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.

Upvotes: 2

Views: 2005

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

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;

Upvotes: 5

Related Questions