T-SQL dynamic sql within while loop

Question

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

edixon · Accepted Answer · 2018-04-26 23:58:17Z

2

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

edited Apr 26, 2018 at 23:58

answered Apr 26, 2018 at 23:47

edixon

1,0107 silver badges16 bronze badges

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

1 Comment

ShaoboPei Over a year ago

That's very helpful. Thank you!

Collectives™ on Stack Overflow

T-SQL dynamic sql within while loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related