Please help with this SQL statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Please help with this SQL statement

Author

Topic

annie_bai@hotmail.com
Starting Member

6 Posts

Posted - 2007-12-04 : 17:32:45

[code]
declare ....
set @parm = 'Q'+rtrim(cast(@sectionID as char))+'_'+rtrim(cast(@qNumber as char))
set @query='select @mean=Avg(cast('+@parm+' as float)) from myTable)--error
exec @query
[/code]
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.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-04 : 17:34:15

exec (@query)

But you should really be using sp_executesql
after reading http://www.sommarskog.se/dynamic_sql.html

E 12°55'05.25"
N 56°04'39.16"

annie_bai@hotmail.com
Starting Member

6 Posts

Posted - 2007-12-04 : 17:37:10

quote:
Originally posted by Peso

exec (@query)

But you should really be using sp_executesql
after reading http://www.sommarskog.se/dynamic_sql.html

E 12°55'05.25"
N 56°04'39.16"

sorry, i did use (query). but still not working.
i'm reading the link...
thanks so much for prompty reply

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-05 : 02:20:50

Did you use @mean as column name?
Read that article fully

Madhivanan

Failing to plan is Planning to fail

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-12-05 : 03:48:20

@mean has to be declared within @query.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2007-12-05 : 04:07:39

Do a DECLARE @mean <data_type> within the statement

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-05 : 04:12:47

[code]declare @parm nvarchar(500),
@query nvarchar(7000),
@mean float

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

exec sp_executesql @query, '@mean float output', @mean output

Select @mean[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

annie_bai@hotmail.com
Starting Member

6 Posts

Posted - 2007-12-05 : 11:35:06

quote:

Originally posted by harsh_athalye

declare @parm nvarchar(500),
	@query nvarchar(7000),
	@mean float

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

exec sp_executesql @query, '@mean float output', @mean output

Select @mean

Thank you! but the code gives unmatch type error:
Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'.
tried to declare @query as text/ntext, but it's not allowed.
I am using SQL2000.

annie_bai@hotmail.com
Starting Member

6 Posts

Posted - 2007-12-05 : 12:06:23

quote:
Originally posted by annie_bai@hotmail.com

quote:
Originally posted by harsh_athalye

declare @parm nvarchar(500),
@query nvarchar(7000),
@mean float

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

exec sp_executesql @query, '@mean float output', @mean output

Select @mean

Thank you so very much!
the problem is solved.
one thing only for others who are reading this thread, the exec line shall be:
exec sp_executesql @query, N'@mean float output', @mean output

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-05 : 12:14:35

[code]declare @parm nvarchar(500),
@query nvarchar(7000),
@mean float

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

exec sp_executesql @query, N'@mean float output', @mean output

Select @mean[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources