Issue with the variables in sql server

Question

Declare @week1 varchar(max)='value 1'
Declare @week2 varchar(max)='value 2'
declare @sql varchar(max)
declare @i int=1
while(@i<=2)
begin
set @sql='print @week'+cast(@i as varchar(6))+''
exec(@sql)
set @i=@i+1
end

After executing this I am getting error saying:

Msg 137, Level 15, State 2, Line 2 Must declare the scalar variable "@week1". Msg 137, Level 15, State 2, Line 2 Must declare the scalar variable "@week2".

take at look [here](https://stackoverflow.com/questions/7181976/must-declare-the-scalar-variable) — Fmanin, Aug 31 '17 at 06:31
SQL which is executed doesn't contain the declaration for week1 or week2. — Marko Juvančič, Aug 31 '17 at 06:35
I think your number of variables is fixed and you can use `CASE WHEN ` to print it. — TriV, Aug 31 '17 at 10:34

score 3 · Answer 1 · answered Aug 31 '17 at 06:41

It's an issue of scope. You can't access variable from within dynamic sql unless you declare them in the dynamic sql. If you change EXEC (@sql) to print(@sql), you'll see that what you're trying to execute is this...

print @week1
print @week2

If you're looking for the following result...

value 1
value 2

... then try altering your code to this...

DECLARE @sql VARCHAR(MAX) = 'DECLARE @week1 VARCHAR(MAX) = ''value 1'', @week2 VARCHAR(MAX) = ''value 2'';';
DECLARE @i INT = 1;
WHILE (@i <= 2)
BEGIN
    SET @sql = @sql + ' print @week' + CAST(@i AS VARCHAR(6)) + '';
    SET @i = @i + 1;
END;
EXEC (@sql);

... which generates and executes this...

DECLARE @week1 VARCHAR(MAX) = 'value 1', @week2 VARCHAR(MAX) = 'value 2'; print @week1 print @week2

i should not hard code the values , actually those are parameters of a procedure here i mentioned like variables. — Karthik Nani, Aug 31 '17 at 06:57

Issue with the variables in sql server

1 Answers1