How to set “SET XACT_ABORT ON ” in a SQL Server tr

问题:

I want to set SET XACT_ABORT ON in a SQL Server 2008R2 stored procedure with a transaction, so do it in a creation script:

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

After successful creation, I check the transaction by clicking "Modify" stored procedure option and in a generated ALTER PROCEDURE script I don't see SET XACT_ABORT ON line:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE MyProc
AS
BEGIN TRAN
...

Where am I wrong or what is the trick? How to correctly define SET XACT_ABORT ON?

回答1:

You normally set xact_abort as part of the body of the stored procedure:

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

There are two "special" settings that are remembered from the session that created the procedure. 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.

So when you create a stored procedure, SQL Server copies the QUOTED_IDENTIFIER option from the connection to the procedure definition. The goal is that someone else with a different QUOTED_IDENTIFIER setting still gets the behavior the author of the procedure intended.

The same is not true for XACT_ABORT.

回答2:

You didn't mention whether or not you are using SQL Management Studio, but if you are and click "Modify" on an existing stored procedure (which I'm assuming is what you did) then MS just generates a boilerplate script based on the contents of the existing stored procedure.

You might consider defining your stored procedures in a separate script file that performs and ALTER PROCEDURE, plus whatever other options you want outside of the sproc (such as SET XACT_ABORT ON). That way you have more control and can just execute the script to update the sproc.