CTE not allowing wrting to local variable

[tshad]

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

 

[gmmastros]

There's an easier way to get a comma delimited list of months...

select  months
from    sys.syslanguages
Where   Name = @@Language

sys.syslanguanges has multiple rows, one for each supported language. @@Language represents the language of the current login, so the query above will work to return a list of months for various languages depending on the language setting of login.

Hope this helps.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tshad]

That's good to know.

But not what I am trying to figure out here. I am trying to figure out how to write to a local variable from within a CTE statement. The list of months is just what I am currently using for a test.

Thanks,

Tom

 

[fredericofonseca]

without knowing exactly what you trying to do its hard to give you a better answer to your problem.

But for the specific example you gave the following works on 2008R2

declare @month varchar(200)
declare @month varchar(200)
;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 + 1 AS varchar(2)) + '/1' + '/' +
		 '2013' AS datetime)) AS MonthName ,
		 MonthPart + 1 AS MonthPart, 2 AS CTEPart
	FROM    CTE1
	WHERE   MonthPart < 12
)
SELECT  @month = SUBSTRING(
			( SELECT  ( ', ' + MonthName )
			  FROM    CTE1
			  FOR XML PATH('')), 3, 1000)
FROM    CTE1 
where ctepart = 1
select @month

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

Also note that I corrected your code as it was outputing January twice

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[tshad]

Actually, if you change the "1 AS MonthPart" to "2 AS MonthPart", it will return 12 rows Jan-Dec and each row will have the same comma delimited string.

The problem is that the comma delimited string was created for each row.

In the 3rd query, I was trying to only create the string once and put it into a local variable. But that gives me an error.

I was trying to find a way using the 2nd CTE to create the string and then instead of doing:

 MonthList = SUBSTRING(
	( SELECT  ( ', ' + MonthName )
	FROM    CTE1
	FOR XML PATH('')), 3, 1000)

I could just do:

@MonthList AS MonthList

But the question is can you write to a local variable. I get an error on the "=" of the assignment. But in a CTE, you can access a local variable, why can you not write to it?

Thanks,

Tom

 

[gmmastros]

You cannot write to a local variable within a CTE.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tshad]

That was what I was trying to find out.

I can't find that anywhere and it is confusing because you can access a local variable from within a CTE.

Thanks,

Tom

 

[philhege]

Did you find Lynn Pettis' answer any more instructive?

-----------
With business clients like mine, you'd be better off herding cats.