Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Writing to local variable from CTE Expand / Collapse
Author
Message
Posted Sunday, May 12, 2013 1:12 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 11:46 AM
Points: 147, Visits: 361
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
Post #1451925
Posted Sunday, May 12, 2013 2:01 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:05 PM
Points: 23,397, Visits: 32,239
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1451930
Posted Sunday, May 12, 2013 2:40 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 11:46 AM
Points: 147, Visits: 361
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.

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

Thanks,

Tom
Post #1451934
Posted Sunday, May 12, 2013 2:43 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:05 PM
Points: 23,397, Visits: 32,239
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?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1451936
Posted Sunday, May 12, 2013 2:53 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 11:46 AM
Points: 147, Visits: 361
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
Post #1451938
Posted Sunday, May 12, 2013 2:57 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:05 PM
Points: 23,397, Visits: 32,239
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;





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1451939
Posted Sunday, May 12, 2013 3:47 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 11:46 AM
Points: 147, Visits: 361
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
Post #1451944
Posted Sunday, May 12, 2013 3:55 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:05 PM
Points: 23,397, Visits: 32,239
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1451945
Posted Sunday, May 12, 2013 4:40 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 11:46 AM
Points: 147, Visits: 361
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
Post #1451949
Posted Sunday, May 12, 2013 4:48 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:05 PM
Points: 23,397, Visits: 32,239
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;





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1451950
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse