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;