When an error stops execution in SQL Server?

Question

If I exec this batch:

begin transaction
    PRINT 'start'
    PRINT 1/0
    PRINT 'continue'
    drop table dbo.tblPrueba
    select * from dbo.tblPrueba
    PRINT 'finish'
rollback transaction

The ouput is this:

start
Msg 8134, Level 16, State 1, Line 3
Divide by zero error encountered.
continue
Msg 208, Level 16, State 1, Line 6
Invalid object name 'dbo.tblPrueba'.

I am forcing two errors: - the first one: PRINT 1/0 (that generates this error:

Msg 8134, Level 16, State 1, Line 3
Divide by zero error encountered.

) And continue executing the batch

- the second one:

drop table dbo.tblPrueba
select * from dbo.tblPrueba

That generates this error:

Msg 208, Level 16, State 1, Line 6
Invalid object name 'dbo.tblPrueba'.

And stops execution of the batch

What is the different between them? Where can I learn those that stop execution and those that doesn´t?

Thanks a lot!!

Answer 1

Since the first error is a divide by zero error, this behavior depends on your ARITHABORT, ARITHIGNORE and ANSI_WARNINGS settings.

From the article:

These three SET commands give you very fine-grained control for a very small set of errors. When a division by zero or an overflow occurs, there are no less four choices.

• No action at all, result is NULL – when ARITHIGNORE is ON.
• Warning message, result is NULL – when all are OFF.
• Statement-termination – when ANSI_WARNINGS is ON.
• Batch-abortion – when ARITHABORT is ON and ANSI_WARNINGS is OFF.

As far as which errors stop execution and which ones don't, please refer to the same article.