Why do conditionals apply to SET NOEXEC ON but not SET NOEXEC OFF?

Of the four select statements in the following script, I would expect the second and third statements to execute and the first and fourth not to execute. The first does not, but the fourth does. It seems the conditional does not apply to SET NOEXEC OFF.

SET NOEXEC OFF
IF (1 = 1) SET NOEXEC ON
SELECT 'Should not see this'
GO
SET NOEXEC OFF
IF (1 = 2) SET NOEXEC ON
SELECT 'Should see this'
GO
SET NOEXEC ON
IF (1 = 1) SET NOEXEC OFF
SELECT 'Should see this'
GO
SET NOEXEC ON
IF (1 = 2) SET NOEXEC OFF
SELECT 'Should not see this, but you do!'
GO

NOEXEC doesn't evaluate the condition in an IF statement, and proceeds to the first statement in the IF block.

So this

SET NOEXEC ON
IF (1 = 2) SET NOEXEC OFF
SELECT 'Should not see this, but you do!'

Turns NOEXEC ON, then immediately turns it OFF and runs the SELECT.

That's why you sometimes see

IF (1 = 2) SET NOEXEC OFF

or similar in read-only procedures that use dynamic SQL. Older tools used NOEXEC ON to determine resultset metadata, which doen't work with dynamic SQL.

StackExchange Database Administrators Q#326519, answer score: 4