reference CTE more than once in a SP

Hi All ...

WITH myCTE (x,y,z) AS (SELECT x,y,z, from myTable)

SELECT x,y,sum(z) from myCTE

SELECT y,z,sum(x) from myCTE

the second SELECT fails, and says invalid object name. does the CTE go out of scope after i reference it once?

never mind the semantics of what I am SELECTing, I just want to be able to reference the CTW more than once in my SP

am I trying to use the CTE in a way that was not intended?

The scope of the CTE is a single statement. So you are almost right, second select statement cannot reference CTE defined in the previous statement. You can use CTE more than once, but only in a single statement:

Code Snippet

WITH myCTE (x,y,z) AS (SELECT x,y,z, from myTable)

SELECT FirstOne.x, FirstOne.y, sum(FirstOne.z)
from myCTE as FirstOne

inner join myCTE as AnotherOne on ...

If you want to refer the resultset in several statements you'll have to create table variable (or temporary table):

Code Snippet

declare @.myTable(x int, y int, z int)
insert @.myTable(x,y,z)
SELECT x,y,z, from myTable

SELECT x, y, sum(z) from @.myTable

SELECT y, z, sum(x) from @.myTable