May 12, 2013 at 1:12 pm
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
May 12, 2013 at 2:01 pm
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.
May 12, 2013 at 2:40 pm
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
May 12, 2013 at 2:43 pm
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?
May 12, 2013 at 2:53 pm
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
May 12, 2013 at 2:57 pm
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;
May 12, 2013 at 3:48 pm
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
May 12, 2013 at 3:55 pm
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.
May 12, 2013 at 4:40 pm
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
May 12, 2013 at 4:48 pm
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;
May 12, 2013 at 4:51 pm
Which comes back to what are you trying to accomplish?
What problem are you trying to solve?
May 12, 2013 at 7:55 pm
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
May 12, 2013 at 8:09 pm
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.
May 12, 2013 at 10:04 pm
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
May 13, 2013 at 12:06 am
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