Can you create nested WITH clauses for Common Table Expressions?
While not strictly nested, you can use common table expressions to reuse previous queries in subsequent ones.
To do this, the form of the statement you are looking for would be
WITH x AS ( SELECT * FROM MyTable), y AS ( SELECT * FROM x)SELECT * FROM y
You can do the following, which is referred to as a recursive query:
WITH yAS( SELECT x, y, z FROM MyTable WHERE [base_condition] UNION ALL SELECT x, y, z FROM MyTable M INNER JOIN y ON M.[some_other_condition] = y.[some_other_condition])SELECT *FROM yYou may not need this functionality. I've done the following just to organize my queries better:
WITH y AS( SELECT * FROM MyTable WHERE [base_condition]),xAS( SELECT * FROM y WHERE [something_else])SELECT * FROM x
With does not work embedded, but it does work consecutive
;WITH A AS(...),B AS(...)SELECT *FROM AUNION ALLSELECT *FROM BEDITFixed the syntax...
Also, have a look at the following example