How to pass user defined variable to Select statement

[anniebai]

In myTable, there're several columns as Avg1, Avg2 ....Avg92, they all are float type.

in my SQL code:

declare @counter int
declare @parm nvarchar(20)
set @counter=0
 
while @counter<10
set @counter=@counter+1
set @parm='Avg'+ cast(@counter as char)
 
Select @parm from myTable   ////// error happens here
 

The last line of the code gives cast error.
How can I get the variable passed correctly into select statement?
thanks in advance.

[deepuv04]

In myTable, there're several columns as Avg1, Avg2 ....Avg92, they all are float type.

in my SQL code:

declare @counter int
declare @parm nvarchar(20)
set @counter=0
 
while @counter<10
set @counter=@counter+1
set @parm='Avg'+ cast(@counter as char)
 
Select @parm from myTable   ////// error happens here
 

The last line of the code gives cast error.
How can I get the variable passed correctly into select statement?
thanks in advance.

use the following query, hope will help you:


declare @counter int
declare @parm nvarchar(20)
set @counter=0
declare @sSql varchar(1000)
while @counter<10
Begin
set @counter=@counter+1
set @parm='Avg'+ cast(@counter as char)

set @sSql = 'Select ' + @parm + ' from myTable '
exec (@sSql)
end

[anniebai]

Thank you, deepuv04.

[anniebai]

in my SQL code:

declare @counter int
declare @parm nvarchar(20)
declare @avg float
set @counter=0
 
while @counter<10
set @counter=@counter+1
set @parm='Avg'+ cast(@counter as char)
 
Select @float=AVG(cast(@parm as float)) from myTable   ////// error happens here
 

The last line of the code gives cast error.
How can I get the variable passed correctly into select statement?
thanks in advance.[/quote]