如何在 SQL Server 事务中设置 "SET XACT_ABORT ON"？

Question

我想在具有事务的SQL Server 2008R2存储过程中设置SET XACT_ABORT ON，因此需要在创建脚本中设置：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET XACT_ABORT ON
GO

CREATE PROCEDURE MyProc
AS
BEGIN TRAN  
    ... 
IF @@ERROR <> 0
BEGIN
    GOTO Done
END 
    ... 
IF @@ERROR <> 0
BEGIN
    GOTO Done
END 
COMMIT TRAN     
Done:
IF @@ERROR <> 0
BEGIN
    ROLLBACK TRAN
END
GO

创建成功后，我通过单击“修改”存储过程选项来检查事务，在生成的ALTER PROCEDURE脚本中，我没有看到SET XACT_ABORT ON行。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE MyProc
AS
BEGIN TRAN
...

我错在哪里，或者说有什么诀窍？如何正确定义SET XACT_ABORT ON？

- rem

---

你在过程定义中没有包含SET语句。由于你使用的是2008版本，应该考虑使用TRY ... CATCH。 - Martin Smith

@Martin Smith：你的意思是完全不包括 SET XACT_ABORT ON，而是通过 TRY..CATCH 捕获错误并明确地执行 ROLLBACK 事务吗？ - rem

参见 这个答案 以及下面的评论，对此有很好的讨论。当TRY...CATCH可用时，我绝对不会在SQL Server 2008上测试@@ERROR，就像你的代码所做的那样。只要提一下，XACT_ABORT在TRY块中有不同的效果。 - Martin Smith

@Martin Smith：谢谢，Martin！我会注意的。 - rem

Answer 1

通常情况下，您需要在存储过程的主体部分设置xact_abort：

CREATE PROCEDURE MyProc
AS
SET XACT_ABORT ON
BEGIN TRAN  
....

有两个“特殊”的设置会记录下创建存储过程的会话。来自MSDN的解释:

存储过程在执行时使用指定的SET设置，除了SET ANSI_NULLS和SET QUOTED_IDENTIFIER。指定SET ANSI_NULLS或SET QUOTED_IDENTIFIER的存储过程使用存储过程创建时指定的设置。如果在存储过程内部使用，则忽略任何SET设置。

因此，当您创建存储过程时，SQL Server会将QUOTED_IDENTIFIER选项从连接复制到过程定义中。这样做的目的是，即使其他人具有不同的QUOTED_IDENTIFIER设置，仍然可以获得过程作者想要的行为。

XACT_ABORT则不适用于此规则。

- Andomar

---

为什么你的 SQL 示例使用 SET XACT_ABORT ON 并且还使用了事务？设置 XACT_ABORT 为 ON 的目的不是让整个过程原子化吗？为什么还需要事务？ - James Wierzba

好的，我看到作者在他的代码示例中已经有了那个。但是，我的先前陈述是否准确？ - James Wierzba

Answer 2

您没有提到是否使用SQL Management Studio，但是如果您使用并且在现有存储过程上单击“修改”（我假设这就是您所做的），那么MS会根据现有存储过程的内容生成一个样板脚本。

您可以考虑在单独的脚本文件中定义存储过程，该文件执行ALTER PROCEDURE以及sproc之外的任何其他选项（例如SET XACT_ABORT ON）。这样，您就可以更好地控制并只需执行脚本即可更新sproc。

- Dan