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".

SQL which is executed doesn't contain the declaration for week1 or week2. — Marko Juvančič
– Marko Juvančič, Commented Aug 31, 2017 at 6:35
I think your number of variables is fixed and you can use CASE WHEN to print it. — TriV
– TriV, Commented Aug 31, 2017 at 10:34

Jason A. Long · Accepted Answer · 2017-08-31 06:41:56Z

3

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

answered Aug 31, 2017 at 6:41

Jason A. Long

4,4421 gold badge14 silver badges18 bronze badges

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

1 Comment

Karthik Nani Over a year ago

i should not hard code the values , actually those are parameters of a procedure here i mentioned like variables.

Collectives™ on Stack Overflow

Issue with the variables in sql server

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related