Sql Server - user CTE in subquery

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?

Answers


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.


Need Your Help

Transformer: transform(Source xmlSource, Result outputTarget)

java xml xslt

I am new to javax.xml.transform » Transformer

android layout messed up suddenly

android android-layout layout

I am in the middle of developing an app and suddenly the layouts became messed up in android 2.2 and 2.3.5. But they look fine in android 4 and up. I made some change in the layouts including some...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.