Option (Recompile) inside of a if exists statement
Suppose you have a bad query plan of a query in "If Exists" statement.
One way to fix it is to tell to the SQL Server to recompile the query
every time the SP executed, by providing Option(Recompile) query hint.
But if you use the Option(Recompile) hint inside of a "If Exists"
statement, you will get a syntax error:
IF EXISTS ( SELECT 1
FROM dbo.SomeTable
WHERE SomeColumn=SomeValue
OPTION(Recompile)
)
PRINT 'Yes'
ELSE
PRINT 'NO'
-----------------------------------------------------------------------
Incorrect syntax near the keyword 'OPTION'.
I do not understand why,if you have an explanation,
please write it in the comments.
There is a workaround(provided by Erland Sommarskog)
for this issue:
DECLARE @Flag BIT
SELECT TOP(1) @Flag=1
FROM dbo.SomeTable
WHERE SomeColumn=SomeValueOPTION(Recompile)
IF @Flag = 1
PRINT 'Yes'
ELSE
PRINT 'No'
One way to fix it is to tell to the SQL Server to recompile the query
every time the SP executed, by providing Option(Recompile) query hint.
But if you use the Option(Recompile) hint inside of a "If Exists"
statement, you will get a syntax error:
IF EXISTS ( SELECT 1
FROM dbo.SomeTable
WHERE SomeColumn=SomeValue
OPTION(Recompile)
)
PRINT 'Yes'
ELSE
PRINT 'NO'
-----------------------------------------------------------------------
Incorrect syntax near the keyword 'OPTION'.
I do not understand why,if you have an explanation,
please write it in the comments.
There is a workaround(provided by Erland Sommarskog)
for this issue:
DECLARE @Flag BIT
SELECT TOP(1) @Flag=1
FROM dbo.SomeTable
WHERE SomeColumn=SomeValueOPTION(Recompile)
IF @Flag = 1
PRINT 'Yes'
ELSE
PRINT 'No'