DECLARE inside Common Table Expression (CTE)

Question

DECLARE inside Common Table Expression (CTE)

epolson

Old Hand

Points: 397
More actions
October 26, 2008 at 2:14 pm

#383234

Can a DECLARE statement be used inside a CTE to create a local variable?

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90348 More actions · Answer 1

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

epolson Old Hand Points: 397 More actions · Answer 2

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.

Lowell SSC Guru Points: 323524 More actions · Answer 3

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

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]

epolson Old Hand Points: 397 More actions · Answer 5

epolson

Old Hand

Points: 397

October 26, 2008 at 4:47 pm

#889789

Done and working. Thanks for the input.

DECLARE inside Common Table Expression (CTE)

Cookies on SQLServerCentral