How to set value to declared variable in SQL Server

Question

How to set value to declared variable in SQL Server.

DECLARE  @V_SEQUENCE INT,  @V_SEQUENCENAME NVARCHAR(MAX);
SET @V_SEQUENCENAME = 'dbo.MYSEQ';   
EXEC('SELECT @V_SEQUENCE = NEXT VALUE FOR  ' + @V_SEQUENCENAME)
SELECT @V_SEQUENCE

Here i am getting an error:

Must declare the scalar variable "@V_SEQUENCE"'

Please tell me how to get result of @V_SEQUENCE.

Answer 1

You want to pass a value out of the execute. I recommend that you use sp_executesql:

DECLARE  @V_SEQUENCE INT, @V_SEQUENCENAME NVARCHAR(MAX), @SQL NVARCHAR(MAX);
SET @V_SEQUENCENAME = 'dbo.MYSEQ'; 
SELECT @SQL = 'SELECT @V_SEQUENCE = NEXT VALUE FOR  ' + @V_SEQUENCENAME;

EXEC sp_executesql @SQL, N'@V_SEQUENCE INT OUTPUT', @V_SEQUENCE = @V_SEQUENCE OUTPUT;

SELECT @V_SEQUENCE;