Passing variables dynamically to sp_executesql

user_13893380 0
Viewed 206

I have a requirement where a set of variables are already initialized to some default values and are being used for other purposes. Now I am trying to pass a few variables dynamically, usually will query the variables from another table and pass to a select statement.

I do not want to use all the variables now. The number of parameters may vary from time to time.

When I try to implement in the below manner I am getting variable not defined.

Below is the sample code snippet which resembles the logic.

DECLARE @i_int1 INTEGER = 1
    ,@i_int2 INTEGER = 2
    ,@i_int3 INTEGER = 3
    ,@i_int4 INTEGER = 4
    ,@i_int5 INTEGER = 5
    ,@i_int6 INTEGER = 6
    ,@sql NVARCHAR(max) = ''
    ,@vcOutput NVARCHAR(max) = ''

BEGIN
    --set @vcOutput = CAST(@i_int1 AS nvarchar(MAX)) +','+ CAST(@i_int2 AS nvarchar(MAX))
    SET @vcOutput = '@i_int1,@i_int2'
    SET @sql = 'select ' + @vcOutput

    EXECUTE sp_executesql @sql;
END
sql-server sql
2 Answers

You can try the following query.

DECLARE @i_int1 INTEGER = 1
    ,@i_int2 INTEGER = 2
    ,@i_int3 INTEGER = 3
    ,@i_int4 INTEGER = 4
    ,@i_int5 INTEGER = 5
    ,@i_int6 INTEGER = 6
    ,@sql NVARCHAR(max) = ''
    ,@vcOutput NVARCHAR(max) = ''

BEGIN
    --set @vcOutput = CAST(@i_int1 AS nvarchar(MAX)) +','+ CAST(@i_int2 AS nvarchar(MAX))
    SET @vcOutput = Convert(Varchar(10), @i_int1) + ',' + Convert(Varchar(10), @i_int2)
    SET @sql = 'select ' + @vcOutput

    --select @sql
    exec(@sql);
END

Live Demo

user_105325000

The reason for this error (Must declare the scalar variable "@i_int1".) is the fact, that sp_executesql expects as parameters the definitions of all parameters embedded in the statement and their actual values. So, you need to pass the definitions and the actual values for each of the defined parameters. Note, that you may define all possible variables as parameters, but do not use all the variables in the statement.

DECLARE 
    @i_int1 int = 1
   ,@i_int2 int = 2
   ,@i_int3 int = 3
   ,@i_int4 int = 4
   ,@i_int5 int = 5
   ,@i_int6 int = 6
   ,@sql nvarchar(max) = ''
   ,@rc int

SET @sql = 'SELECT @i_int1, @i_int2'
EXEC @rc = sp_executesql 
   @sql, 
   N'@i_int1 int, @i_int2 int, @i_int3 int, @i_int4 int, @i_int5 int, @i_int6 int', 
   @i_int1, @i_int2, @i_int3, @i_int4, @i_int5, @i_int6

IF @rc = 0 PRINT 'OK'
ELSE PRINT 'Error'

If the variable names are stored in a table (and if I understand your question correctly), you may try to generate the statement dynamically:

CREATE TABLE Variables (Name nvarchar(128))
INSERT INTO Variables (Name) 
VALUES ('@i_int1'),('@i_int2')

DECLARE 
    @i_int1 int = 1
   ,@i_int2 int = 2
   ,@i_int3 int = 3
   ,@i_int4 int = 4
   ,@i_int5 int = 5
   ,@i_int6 int = 6
   ,@sql nvarchar(max) = ''
   ,@rc int

SET @sql = CONCAT(
   N'SELECT',
   STUFF((SELECT CONCAT(', ', Name) FROM Variables FOR XML PATH('')), 1, 1, N'')
)

EXEC @rc = sp_executesql 
   @sql, 
   N'@i_int1 int, @i_int2 int, @i_int3 int, @i_int4 int, @i_int5 int, @i_int6 int', 
   @i_int1, @i_int2, @i_int3, @i_int4, @i_int5, @i_int6

IF @rc = 0 PRINT 'OK'
ELSE PRINT 'Error'
user_65780800
Related