SQL Dynamic Column in a loop

Question

I want to make the following procedure

declare @a as varchar(10)
set @a= '1'
declare @sql as varchar(1000)
--select @sql ='select convert(float,'+@a+')+1' 
declare @b as varchar(20)
set @b = '5'


select @sql= 'while ('+@a+'<=4)
begin
select ['+@a+'] from #Seg1PD_TruncadaFinal where truncada ='+@a+'
set '+@a+'='+@a+'+1 
end'
exec(@sql)  

I have a table 6x5, where 5 column names are 1,2,3,4,5 and the other is "Truncada" with 5 rows: 1,2,3,4,5. I want to select the column 1 with the row 1 for example and then other combinations, so i tried that. The problem is with when i want to change the value of @a, maybe is because it's a varchar, but this is not a problem with the condition in the while.

Sorry, i wasnt very clear, this is the error I receive:

Msg 102, Level 15, State 1, Line 1574 Incorrect syntax near '1'.

But, when i write the code without de set '+@a+'='+@a+'+1 its run ok with and infinite loop(because it never finish). So i think that the problem is in the set.

Answer 1

The reason that you're getting an infinite loop is because your query is wrong - the variables you are declaring and (you think) editing aren't actually variables anymore when you run the WHILE loop. By concatenating them to a SQL statement that includes the WHILE clause, you are plugging in their values: you just run the same data over and over again. Using PRINT (@SQL), you can see that your actual query is:

while (1<=4) -- this will always be mathematically true
begin
select [1] from #Seg1PD_TruncadaFinal where truncada =1
set 1=1+1 -- this doesn't actually do anything
end

I assume that, for whatever reason, you're trying to do something like the following:

declare @a as varchar(10)
set @a= '1'
declare @sql as varchar(1000)
--select @sql ='select convert(float,'+@a+')+1' 
declare @b as varchar(20)
set @b = '5'

while (@a<=4)
  begin
    select @sql= '
        select ['+@a+'] from #Seg1PD_TruncadaFinal where truncada ='+@a
    exec(@sql)  
    set @a=@a+1 
  end