当错误在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:

我强迫两个错误: - 第一个:PRINT 1/0(生成此错误:

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

非常感谢!!

3 个解决方案

#1

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

由于第一个错误是除零错误,此行为取决于您的ARITHABORT,ARITHIGNORE和ANSI_WARNINGS设置。

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.

这三个SET命令可以为一小组错误提供非常细粒度的控制。当除零或溢出发生时,有四个选择。

No action at all, result is NULL – when ARITHIGNORE is ON.

完全没有动作,结果为NULL - 当ARITHIGNORE为ON时。

Warning message, result is NULL – when all are OFF.

警告消息,结果为NULL - 全部为OFF时。

Statement-termination – when ANSI_WARNINGS is ON.

语句终止 - ANSI_WARNINGS为ON时。

Batch-abortion – when ARITHABORT is ON and ANSI_WARNINGS is OFF.

批量中止 - 当ARITHABORT为ON且ANSI_WARNINGS为OFF时。

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

至于哪些错误停止执行以及哪些错误不执行,请参阅同一篇文章。

#2

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

确保正确处理所有错误的最简单方法是使用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"

如果没有这个,根据ARITHxx,ANSI_WARNINGS和XACT_ABORT等设置,不同的错误可以是语句,范围或批量中止。这在“SQL 2000中的错误处理”中进行了演示和讨论

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

您可以看到不同的(没有更改SET选项)

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

如果我运行两次,我得到这个,因为DROP永远不会执行

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

消息2714,级别16,状态6,行1数据库中已存在名为“tblPrueba”的对象。

#3

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。

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

#1