Using Multiple CTEs

Posted Thursday, October 26, 2006 1:15 PM by C-Dog's .NET Tip of the Day

The process of using multiple CTEs in a stored procedure is kind of wierd, so I thought I would document the syntax. Say you have a situation where you want to create two CTEs and then join the results, you might think you would try something like the following.

WITH MyCTE1 AS
(SELECT Column1, Id FROM Table1);

WITH MyCTE2 AS
(SELECT Column2, Id FROM Table2)

SELECT Colun1, Column2 FROM MyCTE1 
INNER JOIN MyCTE2
    ON MyCTE1.Id = MyCTE2.Id

That flat out will not work. Instead group multiple CTEs together using a comma and omitting subsequent WITH statements.

WITH MyCTE1 AS
(SELECT Column1, Id FROM Table1),
MyCTE2 AS
(SELECT Column2, Id FROM Table2)

SELECT Colun1, Column2 FROM MyCTE1 
INNER JOIN MyCTE2
    ON MyCTE1.Id = MyCTE2.Id

Read the complete post at http://www.dotnettipoftheday.com/blog.aspx?id=319