当 xact_abort 开启时，为什么 Sql Server 在 raiserror 后继续执行？

Why does Sql Server keep executing after raiserror when xact_abort is on?

提问by Eric Z Beard

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

我只是对 TSQL 中的某些东西感到惊讶。我认为如果 xact_abort 开启，调用类似

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

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

将停止执行存储过程（或任何批处理）。

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.

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

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;

The docs say this:

文档是这样说的：

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

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

Does that mean I must be using an explicit transaction?

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

回答by Philip Rieck

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

这是由设计^TM，你可以看到上连接由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.

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

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

是的，对于一些希望RAISERROR高严重性（如16）与 SQL 执行错误相同的人来说，这有点问题- 事实并非如此。

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.

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

回答by ninegrid

If you use a try/catch block a raiserror error number with severity 11-19 will cause execution to jump to the catch block.

如果您使用 try/catch 块，严重性为 11-19 的 raiserror 错误号将导致执行跳转到 catch 块。

Any severity above 16 is a system error. To demonstrate the following code sets up a try/catch block and executes a stored procedure that we assume will fail:

任何高于 16 的严重性都是系统错误。为了演示以下代码，设置了一个 try/catch 块并执行一个我们假设会失败的存储过程：

assume we have a table [dbo].[Errors] to hold errors assume we have a stored procedure [dbo].[AssumeThisFails] which will fail when we execute it

假设我们有一个表 [dbo].[Errors] 来保存错误假设我们有一个存储过程 [dbo].[AssumeThisFails]，当我们执行它时会失败

-- first lets build a temporary table to hold errors
if (object_id('tempdb..#RAISERRORS') is null)
 create table #RAISERRORS (ErrorNumber int, ErrorMessage varchar(400), ErrorSeverity int, ErrorState int, ErrorLine int, ErrorProcedure varchar(128));

-- this will determine if the transaction level of the query to programatically determine if we need to begin a new transaction or create a save point to rollback to
declare @tc as int;
set @tc = @@trancount;
if (@tc = 0)
 begin transaction;
else
 save transaction myTransaction;

-- the code in the try block will be executed
begin try
 declare @return_value = '0';
 set @return_value = '0';
 declare
  @ErrorNumber as int,
  @ErrorMessage as varchar(400),
  @ErrorSeverity as int,
  @ErrorState as int,
  @ErrorLine as int,
  @ErrorProcedure as varchar(128);


 -- assume that this procedure fails...
 exec @return_value = [dbo].[AssumeThisFails]
 if (@return_value <> 0)
  raiserror('This is my error message', 17, 1);

 -- the error severity of 17 will be considered a system error execution of this query will skip the following statements and resume at the begin catch block
 if (@tc = 0)
  commit transaction;
 return(0);
end try


-- the code in the catch block will be executed on raiserror("message", 17, 1)
begin catch
  select
   @ErrorNumber = ERROR_NUMBER(),
   @ErrorMessage = ERROR_MESSAGE(),
   @ErrorSeverity = ERROR_SEVERITY(),
   @ErrorState = ERROR_STATE(),
   @ErrorLine = ERROR_LINE(),
   @ErrorProcedure = ERROR_PROCEDURE();

  insert #RAISERRORS (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure)
   values (@ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorProcedure);

  -- if i started the transaction
  if (@tc = 0)
  begin
   if (XACT_STATE() <> 0)
   begin
     select * from #RAISERRORS;
    rollback transaction;
    insert into [dbo].[Errors] (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure)
     select * from #RAISERRORS;
    insert [dbo].[Errors] (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure)
     values (@ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorProcedure);
    return(1);
   end
  end
  -- if i didn't start the transaction
  if (XACT_STATE() = 1)
  begin
   rollback transaction myTransaction;
   if (object_id('tempdb..#RAISERRORS') is not null)
    insert #RAISERRORS (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure)
     values (@ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorProcedure);
   else
    raiserror(@ErrorMessage, @ErrorSeverity, @ErrorState);
   return(2); 
  end
  else if (XACT_STATE() = -1)
  begin
   rollback transaction;
   if (object_id('tempdb..#RAISERRORS') is not null)
    insert #RAISERRORS (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure)
     values (@ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorProcedure);
   else
    raiserror(@ErrorMessage, @ErrorSeverity, @ErrorState);
   return(3);
  end
 end catch
end

回答by piyush

Use RETURNimmediately after RAISERROR()and it'll not execute the procedure further.

RETURN之后立即使用RAISERROR()，它不会进一步执行该过程。

回答by M?oz

As pointed out on the docs for SET XACT_ABORT, the THROWstatement should be used instead of RAISERROR.

正如在 for 的文档中指出的那样SET XACT_ABORT，THROW应该使用该语句而不是RAISERROR.

The two behave slightly differently. But when XACT_ABORTis set to ON, then you should always use the THROWcommand.

两者的行为略有不同。但是当XACT_ABORT设置为 ON 时，则应始终使用该THROW命令。