Re: Variable and Dynamic SQL
From: Aaron [SQL Server MVP] (ten.xoc_at_dnartreb.noraa)
Date: 07/21/04
- Next message: Sally: "Re: numbering returned rows from query statement"
- Previous message: stper: "Re: alignement data"
- In reply to: David: "Variable and Dynamic SQL"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 21 Jul 2004 11:22:04 -0400
-- http://www.aspfaq.com/ (Reverse address to reply.) "David" <david.paskiet@t-mobile.com> wrote in message news:9abbaed9.0407210721.8273445@posting.google.com... > I need to set a variable value base on the return from an executed > dynamically built sql statement (exec(sql). > > I know that by using the statement set @var1 = (Select count(id) from > table) will produce a value in the variable. So I thought that by > using set @var1 = (exec(sql)) would do the same. WRONG! I get the > 'must declare variable' error. I have found out that the exec runs in > a different space which explains the error - I think. Can somebody > help me answer the problem. I have placed a very simplistic version > of my code below. > > ********************************************* > declare > @iTot integer, > @sSQL varchar(1000) > ; > > -- This was the first thought I had but the error popped up > set @sSQL = 'select @iTot = count(id) from table where field1 <= > getdate()and year(field1) = year(getdate()) ' > /*There will be a where clause built here ans added to the above*/ > execute (@sSQL) > > -- This was the second thought I had but again DNW > set @sSQL = 'select count(id) from table where field1 <= getdate()and > year(field1) = year(getdate()) ' > > set @iTot = (exec(@ssql)) > > ******************************************* > > All other attempts failed as well. Please email me as well as post an > answer. This is a show stopper for me at this moment and very high > priority on my list!!! > > Thanks. > David > 973-292-8956
- Next message: Sally: "Re: numbering returned rows from query statement"
- Previous message: stper: "Re: alignement data"
- In reply to: David: "Variable and Dynamic SQL"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
