3

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
0

1 Answer 1

4

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.

1
  • Got it! That was silly of me! :)
    – Todd Ropog
    Commented Apr 28, 2023 at 0:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.