Prevent scripts generating errors in blocks that aren't executed?

Question

if 1=0
begin
    print 'This should not run'
    select NonexistentField from MyTable
end
else
    print 'This should run'

Inside that code I reference a field that depending on the scenario, may or may not exist. The script is meant to be deployed in varied scenarios, and this line is supposed to be avoided by conditional branching in the cases where it's not applicable. This is meant to be simulated by the 1=0 condition meaning the field is absent in this test, so the code using it is not to be executed.

The thing is, even if the select line doesn't execute, provided MyTable exists, the script throws an

invalid column name

error even before running, and the "This should run" message is never shown.

If, on the other hand, MyTable doesn't exist, the code runs fine and the expected "This should run" is shown.

What sense is there to be made from this?

Is there any way to avoid parts of the script that aren't meant to run generating these errors?

As it is, I find myself unable to have a condition indicate whether a field in a table exists, because the code will fail anyway.

SQL Server has some *interesting* compilation rules. If the object doesn't exist, it allows you to continue creating the script anyway giving you a chance to sort it out before runtime. But if the object does exist, then it must be structurally accurate. — Dale K, Dec 10 '19 at 22:17
You can look at this hack, still not recommended in production code: https://stackoverflow.com/questions/58964952/why-does-cross-apply-not-get-an-invalid-column-error-in-this-query/58965097#58965097 — Piotr Palka, Dec 10 '19 at 22:33

score 2 · Answer 1 · answered Dec 10 '19 at 22:14

Is there any way to avoid parts of the script that aren't meant to run generating these errors?

You need to keep the invalid code from being compiled. So use dynamic SQL.

if 1=0
begin
    print 'This should not run'
    exec ('select NonexistentField from MyTable')
end
else
    print 'This should run'

Prevent scripts generating errors in blocks that aren't executed?

1 Answers1