Writing to local variable from CTE

  • Can I write to a local variable from inside a CTE?

    I am trying to get a comma delimited string of monthly names from inside a CTE to a varchar variable:

    This works fine:

    ;WITH CTE1 AS

    (

    SELECT DATENAME(MM,

    CAST('1' + '/1' + '/' + '2013' AS datetime)) AS MonthName ,

    1 AS MonthPart, 1 AS CTEPart

    UNION ALL

    SELECT DATENAME(MM,

    CAST(CAST(MonthPart AS varchar(2)) + '/1' + '/' +

    '2013' AS datetime)) AS MonthName ,

    MonthPart + 1 AS MonthPart, 2 AS CTEPart

    FROM CTE1

    WHERE MonthPart <= 12

    )

    SELECT [MonthName] ,

    MonthList = SUBSTRING(

    ( SELECT ( ', ' + MonthName )

    FROM CTE1

    FOR XML PATH('')), 3, 1000)

    FROM CTE1

    But this is creating the comma delimited string for each row.

    I wanted to create the string once and use it in the query final query. I tried this:

    ;WITH CTE1 AS

    (

    SELECT DATENAME(MM,

    CAST('1' + '/1' + '/' + '2013' AS datetime)) AS MonthName ,

    1 AS MonthPart, 1 AS CTEPart

    UNION ALL

    SELECT DATENAME(MM,

    CAST(CAST(MonthPart AS varchar(2)) + '/1' + '/' +

    '2013' AS datetime)) AS MonthName ,

    MonthPart + 1 AS MonthPart, 2 AS CTEPart

    FROM CTE1

    WHERE MonthPart <= 12

    )

    SELECT [MonthName] ,

    @MonthList

    FROM CTE1

    This works OK, so I know I can access the variable (but there is nothing in it yet).

    This doesn't work:

    ;WITH CTE1 AS

    (

    SELECT DATENAME(MM,

    CAST('1' + '/1' + '/' + '2013' AS datetime)) AS MonthName ,

    1 AS MonthPart, 1 AS CTEPart

    UNION ALL

    SELECT DATENAME(MM,

    CAST(CAST(MonthPart AS varchar(2)) + '/1' + '/' +

    '2013' AS datetime)) AS MonthName ,

    MonthPart + 1 AS MonthPart, 2 AS CTEPart

    FROM CTE1

    WHERE MonthPart <= 12

    ),

    CTE2 AS

    (

    SELECT @MonthList = SUBSTRING(

    ( SELECT ( ', ' + MonthName )

    FROM CTE1

    FOR XML PATH('')), 3, 1000)

    )

    SELECT [MonthName] ,

    @MonthList

    FROM CTE1

    It won't let me assign the result to the @MonthList variable. But it would work if I took the "@" off, so I know the syntax is correct.

    Is there a way to get this to work to write to variable and then use it at the end?

    Thanks,

    Tom

  • Would you please explain (and by that I mean show) what the output should be. I run your code and see what is generated, but I don't know what it is you are actually trying to accomplish.

  • What this CTE does is create comma separated list of months and put them on each row of months. This would be used for something else. I am just trying to get the list to written to a variable that can be used elsewhere.

    One of the problems is that you can't do a result set that has both assign to a variable and do data retrieval in the same statement.

    This problem seems to be that I can't assign a value to a local variable inside the CTE.

    The first query is fine, except it re-calculates the list for each row. I was trying to only do it once.

    I also can resolve it by changing the variable from @MonthList to MonthList and then do a CROSS JOIN with a DISTICT on the outside query. But then you have the overhead of the JOIN.

    I wanted to do the query to get the months, then do a query using those months to get the list (once) then in the outside query put them together.

    There is a slight error in the 2nd example. That should identical to the 3rd example (that doesn't work) but has the "@" taken off the @MonthList in CTE2.

    MonthNameMonthList

    JanuaryJanuary, January, February, March, April, May, June, July, August, September, October, November, December

    FebruaryJanuary, January, February, March, April, May, June, July, August, September, October, November, December

    MarchJanuary, January, February, March, April, May, June, July, August, September, October, November, December

    AprilJanuary, January, February, March, April, May, June, July, August, September, October, November, December

    MayJanuary, January, February, March, April, May, June, July, August, September, October, November, December

    JuneJanuary, January, February, March, April, May, June, July, August, September, October, November, December

    JulyJanuary, January, February, March, April, May, June, July, August, September, October, November, December

    AugustJanuary, January, February, March, April, May, June, July, August, September, October, November, December

    SeptemberJanuary, January, February, March, April, May, June, July, August, September, October, November, December

    OctoberJanuary, January, February, March, April, May, June, July, August, September, October, November, December

    NovemberJanuary, January, February, March, April, May, June, July, August, September, October, November, December

    DecemberJanuary, January, February, March, April, May, June, July, August, September, October, November, December

    Thanks,

    Tom

  • So, you want a single list in a variable the looks like this:

    January, January, February, March, April, May, June, July, August, September, October, November, December

    Why do you want January listed twice at the front of the list?

  • Actually, that was a mistake that I fixed. Change the line "1 AS MonthPart" in CTE1 to "2 AS MonthPart".

    I hadn't realized what the CTE was doing when I first logged this message.

    The string of months is not the important part.

    What is important is not being able to assign it to a local variable inside a CTE so I can either use it later and/or use it in the outer query without having recalculated each time.

    Thanks,

    Tom

  • tshad (5/12/2013)


    Actually, that was a mistake that I fixed. Change the line "1 AS MonthPart" in CTE1 to "2 AS MonthPart".

    I hadn't realized what the CTE was doing when I first logged this message.

    The string of months is not the important part.

    What is important is not being able to assign it to a local variable inside a CTE so I can either use it later and/or use it in the outer query without having recalculated each time.

    Thanks,

    Tom

    Actually, what you are trying to do is just as important as how you go about it. This is what I have:

    declare @MonthList varchar(256);

    with ShortTally(n) as (select row_number() over (order by (select null)) from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),

    GenerateMonthList(MonthList) as (

    select

    MonthList = stuff((select ',' + datename(month, dateadd(month, n -1, dateadd(year,datediff(year,0,getdate()),0)))

    from ShortTally

    order by n

    for xml path(''),TYPE).value('.','varchar(256)'),1,1,''))

    select @MonthList = MonthList from GenerateMonthList;

    select @MonthList;

  • But you are not assigning the local variable from inside the CTE.

    I know how to do it outside.

    I am not really interested in how to get the monthly list. This could be a list of names.

    But I am trying to see if I can do one select statement that is executed once and assigned to a local variable that I can use in my outside query and display multiple rows of data that will also have the local variable as one of the columns. In my first, query I got the answer I wanted but I didn't want the query that fills the variable executing multiple times.

    I am trying to use one CTE to get some data, then feed that into another CTE that will use that result set to fill a local variable. Then have the outside query display the table from the 1st query along with the variable.

    There are other ways of doing it but I am trying to find out if this way can work.

    Thanks,

    Tom

  • tshad (5/12/2013)


    But you are not assigning the local variable from inside the CTE.

    I know how to do it outside.

    I am not really interested in how to get the monthly list. This could be a list of names.

    But I am trying to see if I can do one select statement that is executed once and assigned to a local variable that I can use in my outside query and display multiple rows of data that will also have the local variable as one of the columns. In my first, query I got the answer I wanted but I didn't want the query that fills the variable executing multiple times.

    I am trying to use one CTE to get some data, then feed that into another CTE that will use that result set to fill a local variable. Then have the outside query display the table from the 1st query along with the variable.

    There are other ways of doing it but I am trying to find out if this way can work.

    Thanks,

    Tom

    Why do you want to assign the value generated in a CTE to a variable? Just writing it in SSMS shows it as a syntax error. I am trying to help you, but I am failing to understand what it is you are trying to accomplish.

  • I know it is giving me an error, which I state.

    The question is: can you assign the result to a local variable from inside the CTE.

    Here is what the 2nd query should have looked like. It is not what I am trying to accomplish. Just demonstrating that the syntax is essentially correct. The only difference is that the query returns the variable to a column "MonthList". This works fine and I can CROSS JOIN to get my result. The one that doesn't work does exactly the same thing except it assigns the result to a local variable "@MonthList".

    ;WITH CTE1 AS

    (

    SELECT DATENAME(MM,

    CAST('1' + '/1' + '/' + '2013' AS datetime)) AS MonthName ,

    1 AS MonthPart, 1 AS CTEPart

    UNION ALL

    SELECT DATENAME(MM,

    CAST(CAST(MonthPart AS varchar(2)) + '/1' + '/' +

    '2013' AS datetime)) AS MonthName ,

    MonthPart + 1 AS MonthPart, 2 AS CTEPart

    FROM CTE1

    WHERE MonthPart <= 12

    ),

    CTE2 AS

    (

    SELECT MonthList = SUBSTRING(

    ( SELECT ( ', ' + MonthName )

    FROM CTE1

    FOR XML PATH('')), 3, 1000)

    )

    SELECT [MonthName] ,

    @MonthList

    FROM CTE1

    The Second CTE is only there to set up the string once - not 12 times as the 1st query does. That is what I am trying to accomplish.

    If you can't write to the local variable fine.

    But then I want to know why not?

    Is it a scope issue?

    You can't do this as the outer query:

    SELECT [MonthName] ,

    MonthList = SUBSTRING(

    ( SELECT ( ', ' + MonthName )

    FROM CTE1

    FOR XML PATH('')), 3, 1000),

    @MonthList = SUBSTRING(

    ( SELECT ( ', ' + MonthName )

    FROM CTE1

    FOR XML PATH('')), 3, 1000)

    FROM CTE1

    It would give you an error of trying to assign to a variable and do data-retrieval at the same time which you can't do.

    But even if it would work, this would give me the result set of the 1st query as well as assign it to the local variable that I can use else where in the procedure. But it also means I need to recalculate the string 24 times (12 for the result set and 12 assignments to the local variable).

    Being able to do the 3rd query would give me the best of both worlds - the result set and assignment of the local variable in one statement.

    Maybe it can't be done. But that is what I am trying to find out.

    Thanks,

    Tom

  • No, it can't.

    A CTE only exists for the duration of the query with which is defined. You can't assign a value in a CTE to a variable for the same reason you can't do this:

    select

    MyCol1,

    @MyVariable = MyCol2

    from

    dbo.MyTable;

  • Which comes back to what are you trying to accomplish?

    What problem are you trying to solve?

  • But you can access a local variable.

    DECLARE @Start int

    SET @Start = 3

    ;WITH NumberSequence ( MonthNumber )

    AS (

    -- Anchor Member

    SELECT @Start AS MonthNumber

    UNION ALL

    -- Recursive Member

    SELECT MonthNumber + 1

    FROM NumberSequence

    WHERE MonthNumber < 12

    )

    -- Outer Query

    SELECT *

    FROM NumberSequence

    I just didn't know you couldn't assign to a variable from a CTE.

    I understand that a CTE only exists for the duration of the query and that would make sense if the variable were inside the CTE (I know it isn't possible) but whether the CTE is temporary doesn't really explain why you can't write to a variable that is outside the CTE, but you can access that same variable in the CTE.

    Thanks,

    Tom

  • This doesn't work either:

    declare @TestVar int;

    select

    Mycol1,

    @TestVar = MyCol2

    from

    dbo.MyTable

    If you took my comment as to the visibility of the CTE, I am sorry, that was not the intent.

    I am still waiting for you to explain what you are trying to accomplish. What is the problem you are trying to solve.

  • I am not sure what you meant there about visibility.

    A CTE only exists for the duration of the query with which is defined. You can't assign a value in a CTE to a variable for the same reason you can't do this:

    I assumed you could write to an outside variable if you can access it (i.e. select as I did).

    Also, you said this doesn't work either. Are you talking about the query I sent last. It does work. If you change the @Start variable, the results are different. But it does access the outside variable.

    On my intent, I think I was pretty clear there. I am just trying to find out if there is a way to write to an outside variable from within a CTE, which may not be possible.

    I was trying to make the first query more efficient by using the outside variable and only building the month string once instead of the 12 times it was being built in the first query.

    Thanks,

    Tom

  • tshad (5/12/2013)


    I am not sure what you meant there about visibility.

    A CTE only exists for the duration of the query with which is defined. You can't assign a value in a CTE to a variable for the same reason you can't do this:

    I assumed you could write to an outside variable if you can access it (i.e. select as I did).

    Also, you said this doesn't work either. Are you talking about the query I sent last. It does work. If you change the @Start variable, the results are different. But it does access the outside variable.

    On my intent, I think I was pretty clear there. I am just trying to find out if there is a way to write to an outside variable from within a CTE, which may not be possible.

    I was trying to make the first query more efficient by using the outside variable and only building the month string once instead of the 12 times it was being built in the first query.

    Thanks,

    Tom

    Then build it once like I showed you how with the code I had posted and then use the variable in a subsequent query. Remember, set-based doesn't always mean in one query. Sometimes you need to divide and conquer to solve a problem in the most efficient and scalable manner.

Viewing 15 posts - 1 through 14 (of 14 total)

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