Sql Server - user CTE in subquery
This question has been asked before -
The only answer suggested was "Just define your CTE on top and access it in the subquery?"
This works, but I would really like to be able to use a CTE in the following scenarios -
as a subquery in a SELECT
as a derived table in the FROM clause of a SELECT
Both of these work in PostgreSQL. With Sql Server 2005, I get "Incorrect syntax near the keyword 'with'".
The reason I would like it is that most of my queries are constructed dynamically, and I would like to be able to define a CTE, save it somewhere, and then drop it in to a more complex query on demand.
If Sql Server simply does not support this usage, I will have to accept it, but I have not read anything that states that it is not allowed.
Does anyone know if it is possible to get this to work?
In SQL Server, CTE's must be at the top of the query. If you construct queries dynamically, you could store a list of CTE's in addition to the query. Before you send the query to SQL server, you can prefix the query with a list of CTE's:
; with Cte1 as (...definition 1...), Cte2 as (...definition 2...), Cte3 as (...definition 3...), ... ...constructed query...
This is assuming that you're constructing the SQL outside of SQL Server.
You could also consider creating views. Views can contain CTE's, and they can be used as a subquery or derived table. Views are a good choice if you generate SQL infrequently, say only during an installation or as part of a deployment.