CODEWITHSUNDEEP
sql-servert-sqlwhile-loopdynamic-sql
ShaoboPei
ShaoboPei

Reputation: 13

T-SQL dynamic sql within while loop

I would like to use T-SQL while loop to get var_1, var_2, var_3 individually at each loop. But, it returns error message "Must declare the scalar variable "@var_1","@var_2","@var_3". Could please help me out. Thank you. I attached my code below:

declare @var_1 varchar(max)
set @var_1 = 'abcdef'
declare @var_2 varchar(max)
set @var_2 = 'ghijk'
declare @var_3 varchar(max)
set @var_3 = 'lmnopq'
declare @counter tinyint
set @counter = 1
declare @termName varchar(max)
while @counter<=3
begin

    set @termName = '@var_' + CONVERT(varchar(10), @counter)
    print @termName
    declare @sql_code varchar(max)
    set @sql_code = '
       print '+ @termName+';
    '
    print @sql_code
    exec (@sql_code)

    set @counter = @counter + 1
end

Upvotes: 1

Views: 1014

Answers (1)

edixon
edixon

Reputation: 1000

When you use EXEC with a string, the command is carried out in a new session, so variables cannot be used pass arguments or get results. However, you could create a temporary table, put the arguments in it and use this table inside the dynamic statement:

create table #T (val_1 varchar(10), val_2 varchar(10), val_3 varchar(10));
insert into #T values ('abcef', 'ghijk', 'lmnopq');
declare @counter tinyint
set @counter = 1
while @counter<=3
begin
    declare @sql_code varchar(max)
    set @sql_code = '
       declare @v varchar(10);
       select @v = val_' + CONVERT(varchar(10), @counter) + ' FROM #T;
       print @v;
    '
    print @sql_code
    exec (@sql_code)
    set @counter = @counter + 1
end

Upvotes: 2

Related Questions