CODEWITHSUNDEEP
sqlsql-server
Chanky Mallick
Chanky Mallick

Reputation: 587

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

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 ?

Upvotes: 0

Views: 495

Answers (2)

Dan Guzman
Dan Guzman

Reputation: 46203

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.

Upvotes: 1

Tom
Tom

Reputation: 747

Most likely because the ALTER TABLE statements actually touch the data in the given tables. Removing the index does not have that impact, so I guess SQL decides it is OK to continue with the next statement.

Upvotes: 0

Related Questions