I stand corrected on the multiple CTE,s in one statement. I should have said that with that one CTE's cannot be used in more than one statement.

With nesting I meant using CTE definitions in subqueries. You will have to move the CTE to the outer beginning of the query. From a functional pov, this might not so much be a problem, but from a logical and scalability pov its very different (inferior) as with a normal select statement.

I do a lot of automatic creation of dynamic SQL, and one of the nice things with straight SQL SELECT is that you can take the result as source table on a FROM, JOIN, IN, EXITS or Field Expression on an outer SQL Select.

SELECT * FROM (SELECT ... FROM Table WHERE .... ) As x

But you cannot do

SELECT * FROM (WITH MYCTE  AS (SELECT ... FROM ... WHERE ...) ) as x

And that is really a shame as it loses one of the very strong points of SQL SELECT. The ability to reuse the output of a statement as the input for the outer. Instead you will have to do

WITH MYCTE AS  (SELECT * FROM (SELECT ... FROM ... WHERE ...) as x)

which is really a roadblock if you want to create complex SQL statements from smaller building blocks. Its easy to build those (think LEGO) building blocks without CTE's. With CTE's it becomes about a mission impossible.

See http://social.msdn.microsoft.com/Forums/sqlserver/en-US/9eb8d3aa-17fe-4c2e-bfdb-04573107ae6d/cte-query-as-derived-table for further discussion.


Walter,


>Aside from that, CTE's cannot be used as a total replacement of temp tables which are derived from more complex statement as you cannot nest CTE's and cannot use two CTE's in one SQL statement.
>
>Walter, I didn't initially read this sentence in full, until Naomi replied. Again, yes, CTEs are not a full replacement for temp tables. However, that last part ("cannot nest CTEs and cannot use two CTEs in one SQL statement") is definitely not true.
>
>I refer you to a message I posted about a month ago, Message #1593237
>It's an example I use when showing how to aggregate in a scenario of one master table and multiple child tables. You can create multiple CTEs, delimited by a comma, and then reference them in the final SELECT.
>
>(Yes, you can accomplish the same solution with actual inline derived table subqueries)
>
>

>;with POSales as   -- CTE 1
>        ( select ShipMethodID, sum(TotalDue)  as POTotal 
>		        from Purchasing.PurchaseOrderHeader 
>				       group by ShipMethodID) ,   -- note the 2 CTEs, separated by a comma
>   SOSales as      -- CTE 2
>        (Select ShipMethodID, sum(TotalDue) as SOTotal 
>		          from Sales.SalesOrderHeader 
>				       group by ShipMethodID)
>
>   select ShipMethod.Name, POSales.POTotal, Sosales.SOTotal
>   from Purchasing.ShipMethod
>      left outer join POSales on ShipMethod.ShipMethodID = POSales.ShipMethodID    -- reference to cte1
>      left outer join SOSales on ShipMethod.ShipMethodID = SOSales.ShipMethodID     -- reference to cte2
>

>
>And while I don't use this one very often, you can do...
>
>
>;with TempCTE1 as (select * from TableA where somecondition),
> TempCTE2 as (select * from TempCTE1 where someadditionalcondition),
> tempCTE3 as (select * from TempCTE2 where yetanothercondition)
>
>select * from TempCTE3 join (some other table) etc etc