CODEWITHSUNDEEP
sql-serversql-server-2008
Karthik Nani
Karthik Nani

Reputation: 11

Issue with the variables in sql server

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

Upvotes: 1

Views: 133

Answers (1)

Jason A. Long
Jason A. Long

Reputation: 4442

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

Upvotes: 3

Related Questions