Sql Server - user CTE in subquery

Question

This question has been asked before -

How we can use CTE in subquery in sql server?

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 -

1. as a subquery in a SELECT

2. 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?

Answer 1

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.

Answer 2

SQL Server does not support this much-required feature. I too have been looking for help on this. MS SQL Server does not support Temporary Views either as opposed to PostgreSQL. The above-mentioned solution is also likely to work only if all the CTE definitions could be generated before-hand and do not have conflicting names in each of the sub-queries either - the purpose being that these CTE definitions may be different for each level of a sub-query.

Sad but true !!!

Regards, Kapil