I have noticed something else interesting about this that you should watch out for.

If you use SET FMTONLY OFF as the last statement in the true statement block of an IF condition then the ELSE statement block of the IF condition will execute regardless of whether the IF condition was true or false.

For example:

Both blocks execute

IF 1 = 1

BEGIN

SET FMTONLY ON

SELECT 1 AS [true block]

SET FMTONLY OFF

END

ELSE

BEGIN

SELECT 1 AS [else block]

END

And it doesn't matter when the SET FMTONLY ON occurs, again

Both blocks execute

SET FMTONLY ON

IF 1 = 1

BEGIN

SELECT 1 AS [true block]

SET FMTONLY OFF

END

ELSE

BEGIN

SELECT 1 AS [else block]

END

However, if you have a statement following the SET FMTONLY OFF, then things work the way you expect

Only the true block executes

SET FMTONLY ON

IF 1 = 1

BEGIN

SELECT 1 AS [true block]

SET FMTONLY OFF

SET ANSI_NULLS ON

END

ELSE

BEGIN

SELECT 1 AS [else block]

END

And the statement after the SET FMTONLY OFF can even be a second SET FMTONLY OFF!

Only the true block executes

SET FMTONLY ON

IF 1 = 1

BEGIN

SELECT 1 AS [true block]

SET FMTONLY OFF

SET FMTONLY OFF

END

ELSE

BEGIN

SELECT 1 AS [else block]

END

Or you can code the ELSE block with its own IF condition

Only the true block executes

SET FMTONLY ON

IF 1 = 1

BEGIN

SELECT 1 AS [true block]

SET FMTONLY OFF

END

ELSE IF 1 <> 1

BEGIN

SELECT 1 AS [else block]

END