TSQL IF appears to randomly execute code from wrong logic branch-CodePudding

I have an idempotent script that brings different database deployments up to the latest revision. The column referenced in the code is actually deleted in a future release, so in order to preserve the script's validity I check if the column exists before executing the statement. I noticed some unexpected behaviour whereby the IF logic branches seem to intermittently execute when the condition is false.

Basic example

IF (1 = 0)
    BEGIN
        PRINT 'Adding data'; -- Never printed
    END
GO

Testing the logic branch

IF (1 = 0)
    BEGIN
        PRINT 'Adding data';                       -- Never printed
        RAISERROR('One never equals zero', 16, 1); -- Never raised
    END
GO

Added data modification code

IF (1 = 0)
    BEGIN
        PRINT 'Adding data';                                            -- Never printed
        RAISERROR('One never equals zero', 16, 1);                      -- Never raised
        INSERT INTO [tbl_Users] ( NonExistentColumn ) VALUES ( 'Bar' ); -- Msg 207, Level 16, State 1, Line 6; Invalid column name 'NonExistentColumn'.
    END
GO

Why does this happen? Particularly, why are the PRINT and RAISERROR lines correctly ignored but the INSERT still executes, even though the entire logic branch should be ignored?

CodePudding user response：

None of your statements are run, it isn't skipping the PRINT and RAISERROR and going to the INSERT INTO, instead the entire batch fails due to a compilation error.

Despite some beliefs, T-SQL is parsed and validated before it is executed, and if the statement isn't valid then the entire batch won't be run; this is why when you have a syntax error, for example, none of the statements are run.

Here you have an invalid column reference, the column NonExistentColumn in the table [YourDefaultSchema].tbl_Users doesn't exist. Although SQL Server does have some deferred object validation, columns against objects that exist are not one of them. As such, when you ask for your batch to be executed, the parser checks to see table [YourDefaultSchema].tbl_Users has a column NonExistentColumn, and when it finds it doesn't, it raises an "Invalid Column Name" error.

If the table itself did not exist, then this error would not occur. For example, take the following batches:

CREATE TABLE dbo.SomeTable (SomeColumn int);
GO
PRINT N'First Batch Starts';    
IF 1 = 0 BEGIN
    PRINT N'Entered first IF.';
    INSERT INTO dbo.SomeTable (AnotherColumn)
    VALUES(1);
END;
PRINT N'First Batch Ends';    
GO
PRINT N'Second Batch Starts';    
IF 1 = 0 BEGIN
    PRINT N'Entered second IF.';
    INSERT INTO dbo.AnotherTable (AnotherColumn)
    VALUES(1);
END;
PRINT N'Second Batch Ends';
GO

DROP TABLE dbo.SomeTable;

In the messages pane of SSMS you will get the following:

Msg 207, Level 16, State 1, Line 6
Invalid column name 'AnotherColumn'.
Second Batch Starts
Second Batch Ends

Notice that none of the PRINT statements from the first batch print, as the batch isn't run, however, in the second batch they are, as the validation of the column is deferred till that statement would be run (and it's never run, so no error occurs).

If you wanted to reference a potentially non-existent column on an existing object, defer the statement's validation using sys.sp_executesql:

CREATE TABLE dbo.SomeTable (SomeColumn int);
GO
PRINT N'Third Batch Starts';    
IF 1 = 0 BEGIN
    PRINT N'Entered first IF.';
    EXEC sys.sp_executesql N'INSERT INTO dbo.SomeTable (AnotherColumn) VALUES(1);';
END;
PRINT N'Third Batch Ends';    
GO

DROP TABLE dbo.SomeTable;

This simply prints the following:

Third Batch Starts
Third Batch Ends

db<>fiddle