Allow Common Table Expressions in all query expressions - by Hugo Kornelis

Status : 

 


49
1
Sign in
to vote
ID 308744 Comments
Status Active Workarounds
Type Suggestion Repros 3
Opened 11/6/2007 1:16:35 AM
Access Restriction Public

Description

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:

IF EXISTS
(
WITH tab AS
(SELECT 1 AS col)
SELECT * FROM tab
)
PRINT 'okiedokie'
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.
Sign in to post a comment.
Posted by Microsoft on 12/3/2007 at 1:42 PM
Dear Hugo

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.

Best regards,
Eric Hanson
Posted by Microsoft on 11/14/2007 at 11:20 AM
Hello,

Thank you for sending your feedback to us. We will consider adding this functionality in a future release of SQL Server.

Thanks,
Vineet Rao