当 xact_abort 开启时，为什么 Sql Server 在 raiserror 后继续执行? [英] Why does Sql Server keep executing after raiserror when xact_abort is on?

问题描述

我刚刚对 TSQL 中的某些内容感到惊讶.我认为如果 xact_abort 开启，调用类似

I just got surprised by something in TSQL. I thought that if xact_abort was on, calling something like

raiserror('Something bad happened', 16, 1);

将停止执行存储过程(或任何批处理).

would stop execution of the stored procedure (or any batch).

但我的 ADO.NET 错误消息正好相反.我在异常消息中收到了 raiserror 错误消息，以及之后发生的下一件事情.

But my ADO.NET error message just proved the opposite. I got both the raiserror error message in the exception message, plus the next thing that broke after that.

这是我的解决方法(无论如何这是我的习惯)，但似乎没有必要:

This is my workaround (which is my habit anyway), but it doesn't seem like it should be necessary:

if @somethingBadHappened
    begin;
        raiserror('Something bad happened', 16, 1);
        return;
    end;

文档是这样说的:

当 SET XACT_ABORT 为 ON 时，如果 Transact-SQL 语句引发运行时错误，则整个事务将终止并回滚.

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

这是否意味着我必须使用显式事务?

Does that mean I must be using an explicit transaction?

推荐答案

This is By Design^TM，正如您在 连接 SQL Server 团队对类似问题的回答:

This is By Design^TM, as you can see on Connect by the SQL Server team's response to a similar question:

感谢您的反馈.按照设计，XACT_ABORT 设置选项不会影响 RAISERROR 语句的行为.我们将考虑您的反馈，以便为 SQL Server 的未来版本修改此行为.

Thank you for your feedback. By design, the XACT_ABORT set option does not impact the behavior of the RAISERROR statement. We will consider your feedback to modify this behavior for a future release of SQL Server.

是的，对于那些希望具有高严重性(例如 16)的 RAISERROR 与 SQL 执行错误相同的人来说，这有点问题 - 它是不是.

Yes, this is a bit of an issue for some who hoped RAISERROR with a high severity (like 16) would be the same as an SQL execution error - it's not.

您的解决方法就是您需要做的事情，并且使用显式事务不会对您想要更改的行为产生任何影响.

Your workaround is just about what you need to do, and using an explicit transaction doesn't have any effect on the behavior you want to change.

这篇关于当 xact_abort 开启时，为什么 Sql Server 在 raiserror 后继续执行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！