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