Allow Common Table Expressions in all query expressions
Currently, a Common Table Expression (CTE) is allowed only in query statements (SELECT, INSERT, UPDATE, and DELETE). According to the SQL:2003 standard, a CTE (or "<with clause>", as the standards call it) should be allowed in all query expression, which includes subqueries. This would enable us to write, for instance:
WITH tab AS
(SELECT 1 AS col)
SELECT * FROM tab
ELSE PRINT 'not ok';
This syntax, which is allowed according to SQL:2003, is not accepted by SQL Server 2005. I have reported this as a bug (see https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254336), but this has been closed as by design.
Please change your design. I see no reason not to implement the with clause as specified in the SQL:2003 standard.
Upvotes: 47<=-=Nov 14 2007 11:20AM=-=>
Thank you for sending your feedback to us. We will consider adding this functionality in a future release of SQL Server.
Thank you for your feedback. I really like your suggestion and I can see how useful it could be. Unfortunately, we’re at a point in the release cycle for SQL Server 2008 where we’re not able to address this issue as part of the release. We’ll consider it for the next release.