October 26, 2008 at 2:14 pm
Can a DECLARE statement be used inside a CTE to create a local variable?
October 26, 2008 at 3:03 pm
No, what exactly are you trying to accomplish? If you can explain why you think you need a 'local' variable inside a CTE somebody here can definitely point you in the right direction for a better solution.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 26, 2008 at 3:50 pm
I'm trying to create a CTE with variables for certain values that I need to be able to change on a whim (there are over 40 instances of each of these values in the LARGE query I'm testing). I'm currently using hard coded values, and it appears that that is the only solution I will find. It was more of a convenience, than a need.
October 26, 2008 at 4:08 pm
can't you just either declare the variables and use them in teh cte query, or throw it into a procedure with your 40 parameters with default values?
here's a very basic CTE example, where i use a variable, but that is for the WHERE statment... are you saying you want the columns used in the CTE to bevariable? if that's true, you'd have to use dynamic sql.
declare @cnt integer;
set @cnt=40;
with CTE (name, Row) as
(select name, row_number() over (order by id)
from syscolumns)
select syscolumns.*
from dbo.syscolumns
inner join CTE
on CTE.name = syscolumns.name
where CTE.Row = @cnt;
Lowell
October 26, 2008 at 4:29 pm
DECLARE is a statement. A CTE is an expression. In SQL, expressions are contained within statements, and never the other way around.
Unless you are trying to make a view, you should be able to just put your DECLARE statements before the statement that contains your CTE's. As long as they are in the same batch, this should work.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 26, 2008 at 4:47 pm
Done and working. Thanks for the input.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy