DECLARE inside Common Table Expression (CTE)

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

  • 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

  • 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.

  • 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!

  • 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]

  • 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