SQL Server behave differently to stop Execution in different types of error

Question

I have list of Alter Table statements:

BEGIN

    ALTER TABLE TABLE1 ALTER COLUMN AA INT -- Error Here
    ALTER TABLE TABLE1 ALTER COLUMN BB INT
    PRINT('CONTINUE AFTER ERROR')

END

After error its stopped execution and skipped other statements.

In output it shows only 1 error.

But in 2nd case where i have a list of DROP INDEX Statements

BEGIN
    DROP INDEX TABLE1.INDEX1  -- Error Here
    DROP INDEX TABLE2.INDEX2
    PRINT('CONTINUE AFTER ERROR')
END

Here after error, it continues execution and prints error log and the text 'CONTINUE AFTER ERROR'.

Why this difference ?

question from:https://stackoverflow.com/questions/65641873/sql-server-behave-differently-to-stop-execution-in-different-types-of-error

Answer 1

The difference in behavior is because the first batch of ALTER TABLE statements is a compilation error whereas the second batch of DROP INDEX statements is a runtime error.

When a compilation error occurs on a batch, no code executes and only the compilation error is returned. Also, since no code executes with a compilation error, the error cannot even be caught with structured error handling:

BEGIN TRY
        ALTER TABLE TABLE1 ALTER COLUMN AA INT -- Error Here
        ALTER TABLE TABLE1 ALTER COLUMN BB INT
        PRINT('CONTINUE AFTER ERROR')
END TRY
BEGIN CATCH
    PRINT 'CAUGHT ERROR';
END CATCH;

Msg 4902, Level 16, State 1, Line 4 Cannot find the object "TABLE1" because it does not exist or you do not have permissions.

When compilation is successful and a runtime error happens, subsequent statements in the same batch may or may not execute after an error depending the error severity and XACT_ABORT setting.