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

Query hints not allowed after or inside IF EXISTS()

Hugo Kornelis came up with an interesting problem when combining IF EXISTS() and certain query hints (e.g. to limit parallelism or to suppress warning messages).

Microsoft is already aware of the issue internally, but I thought it would be beneficial to post it here both for visibility of the bug itself, and to expose the workaround I came up with (and any you may come up with, also).

Basically, the problem is this. If I want to take this query, which works fine:

SELECT *
FROM sys.objects
WHERE [name] LIKE '%foo%'
OPTION (FORCE ORDER);

And put it inside an IF EXISTS conditional, like one of these examples:

IF EXISTS
(
SELECT 1
FROM sys.objects
WHERE [name] LIKE '%foo%'
OPTION (FORCE ORDER)
)
PRINT 'yes';
ELSE
PRINT 'no';

IF EXISTS
(
SELECT 1
FROM sys.objects
WHERE [name] LIKE '%foo%'
) OPTION (FORCE ORDER)
PRINT 'yes';
ELSE
PRINT 'no';

I get the following error messages:

Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'OPTION'.
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'ELSE'.

2 votes
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 →

    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