Query hints not allowed after or inside IF EXISTS() - by AaronBertrand

Status : 

 


31
0
Sign in
to vote
ID 270480 Comments
Status Active Workarounds
Type Bug Repros 1
Opened 4/12/2007 10:38:53 AM
Access Restriction Public

Description

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'.
Sign in to post a comment.
Posted by Microsoft on 12/7/2007 at 1:58 PM
Hello,

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

Thanks,
-Vineet Rao