Sql Server - custom CTE in a subquery

Question

This question has been asked before -

How can we use CTE in a subquery in sql server?

The only possible answer is: "Just define your CTE from above and access it in the subquery?"

This works, but I would really like to be able to use CTE in the following scenarios -

• as a subquery in SELECT

• as a view in a FROM SELECT clause

Both of these works in PostgreSQL. With Sql Server 2005, I get "Invalid syntax next to the keyword" c "".

The reason I would like most of my queries to be built dynamically, and I would like to be able to define a CTE, save it somewhere and then forward it to a more complex query upon request.

If Sql Server simply does not support this use, I must accept it, but I have not read anything that states that it is not allowed.

Does anyone know if this can be made to work?

Answer 1

In SQL Server, the CTE should be at the top of the query. If you build queries dynamically, you can save the CTE list in addition to the query. Before sending a query to the SQL server, you can prefix the query using the CTE list:

; with Cte1 as (...definition 1...), Cte2 as (...definition 2...), Cte3 as (...definition 3...), ... ...constructed query... 

It is assumed that you are creating SQL outside of SQL Server.

You may also consider creating views. Views can contain CTEs and can be used as a subquery or view. Views are a good choice if you rarely generate SQL, say, only during installation or as part of a deployment.

Answer 2

SQL Server does not support this much needed feature. I was also looking for help with this. MS SQL Server does not support temporary views, not PostgreSQL. The above solution is also likely to work only if all CTE definitions can be generated in advance and do not have conflicting names in each of the subqueries: the goal is that these CTE definitions may differ for each sub-query level.

Sad but true !!!

Regards, Kapil