Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

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:

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.

1 vote
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    AdminMicrosoft SQL Server (Admin, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
    unplanned  ·  AdminMicrosoft SQL Server (Admin, Microsoft Azure) responded  · 

    Upvotes: 47

    <=-=Nov 14 2007 11:20AM=-=>

    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

    <=-=Dec 3 2007 1:42PM=-=>

    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

    0 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...

      Feedback and Knowledge Base