CODEWITHSUNDEEP
sql-serverexecution
user1221392
user1221392

Reputation: 75

When an error stops execution in SQL Server?

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!!

Upvotes: 5

Views: 3004

Answers (3)

Pankaj
Pankaj

Reputation: 10105

Where can I learn those that stop execution

You can use exception handling

Begin try
  begin transaction
    PRINT 'start'
    PRINT 1/0
    PRINT 'continue'
    create table #t
    ( 
       id int
    )
    drop table #t
    select * from #t
    PRINT 'finish'
  rollback transaction
End Try

Begin Catch
   if( XACT_STATE() == 1)
     Rollback Tran
End Catch

You can use Set XACT_ABORT ON like below.

Set XACT_ABORT ON
begin transaction
    PRINT 'start'
    PRINT 1/0
    PRINT 'continue'
    create table #t
    ( 
       id int
    )
    drop table #t
    select * from #t
    PRINT 'finish'
rollback transaction

Upvotes: 1

gbn
gbn

Reputation: 432271

The easiest way to ensure all errors are handled correctly is to use TRY/CATCH

Without this, different errors can be statement, scope or batch aborting depending on settings such as ARITHxx, ANSI_WARNINGS and XACT_ABORT. This is demonstrated and discussed at "Error Handling in SQL 2000"

You can see the different (no SET options changed) with this

CREATE TABLE dbo.tblPrueba (gbn int);
GO

BEGIN TRY

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

END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE();
    IF XACT_STATE() <> 0 rollback transaction
END CATCH

If I run this twice, I get this because the DROP is never executed

Msg 2714, Level 16, State 6, Line 1
There is already an object named 'tblPrueba' in the database.

Upvotes: 4

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

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.

Upvotes: 10

Related Questions