sql-server - 如何在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？

Answer 1

您通常将 xact_abort 设置为存储过程主体的一部分:

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

创建该过程的 session 中会记住两个“特殊”设置。 Explanation from MSDN:

Stored procedures execute with the SET settings specified at execute time except for SET ANSI_NULLS and SET QUOTED_IDENTIFIER. Stored procedures specifying SET ANSI_NULLS or SET QUOTED_IDENTIFIER use the setting specified at stored procedure creation time. If used inside a stored procedure, any SET setting is ignored.

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

对于 XACT_ABORT 来说情况并非如此。