Can I use OPTION (MAXDOP 1) for a query within an IF statement?-CodePudding

I want to preface this with the fact that I do understand that using OPTION (MAXDOP) is generally frowned upon and undesirable. However, just saying "don't use OPTION (MAXDOP)" does not address the narrow focus of the question at hand. For purposes of this question, say I have a scenario where I am backed into a corner, and this is something I'm stuck with due to other issues that are beyond what I actually want to ask for this specific question. Let's not get stuck on those reasons for the time being please. Specifically for this question, I am writing a stored procedure wherein I have an IF statement something like:

IF ((SELECT TOP 1 Identifier 
     FROM SomeTable 
     WHERE <some conditions>) IS NOT NULL)
BEGIN 
    <do some stuff>
END

I would like the query within the IF statement to run with OPTION (MAXDOP 1) - again, I know this is generally not what you'd want to do, let's save that discussion for the sake of this question. Can I do this? It seems like whether I put the OPTION (MAXDOP 1) bit at the end of the line with the IF, immediately after IS NOT NULL, or after <some conditions>, my query editor is telling me it's not allowed there. Is there somewhere I can put it to make this occur?

CodePudding user response：

Interesting. It seems like you cannot use query hints on a query enclosed in parenthesis that is not a derived table - that is, one that expects a single value. Actually, I do not know how this clause is called to search for it through the documentation, so more enlightening answers are welcome.

Still, you can use a variable to do what you want:

SELECT TOP 1 @identifier=Identifier 
FROM SomeTable 
WHERE <some conditions>
option (maxdop 1)

IF @identifier IS NOT NULL......