Please help with this SQL statement

[anniebai]

declare @parm nvarchar (20)
declare @mean float

set @parm = 'Q' + rtrim(@qNumber)
set @query='select @mean=Avg(cast('+@parm+' as float)) from myTable)
exec @query

error msg:Must declare the variable '@mean'.

the message is confusing, because @mean is declared at the first line. Can anybody explain a little bit. Thanks for any inputs.

[Jim Doherty]

declare @parm nvarchar (20)
declare @mean float
 
set @parm = 'Q' + rtrim(@qNumber)
set @query='select @mean=Avg(cast('+@parm+' as float)) from myTable)
exec @query

error msg:Must declare the variable '@mean'.

the message is confusing, because @mean is declared at the first line. Can anybody explain a little bit. Thanks for any inputs.

Hi Annie,

Yes but @mean has no meaning if used within the string as given there? something like the below returns a column value if this is what you are trying to do? I've set @qNumber because I don't know where qNumber derives its source value from in your example.
In addition, in your example you are trying to cast to numeric 'float' a concatenated 'Q' with a numeric value? can't be done am afraid numeric is numeric if I am understanding your example logic correctly?

declare @qNumber nvarchar(45)
declare @parm nvarchar (20)
declare @mean float
set @qNumber='300 '
set @parm = rtrim(@qNumber)
select @mean=Avg(cast( @parm as float)) from myTable
select @mean as mycolumn

Regards

Jim :)