How to set "SET XACT_ABORT ON" in a SQL Server transaction?

Question

I want to set SET XACT_ABORT ON to a SQL Server 2008R2 stored procedure with a transaction, so do this in the creation of a 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 checked the transaction by clicking the "Change" option of the stored procedure and in the generated ALTER PROCEDURE script, I do not see the SET XACT_ABORT ON :

 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE MyProc AS BEGIN TRAN ... 

Where am I wrong or what's the trick? How to correctly determine SET XACT_ABORT ON ?

Answer 1

Usually you 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” parameters that are remembered from the session that created the procedure. Explanation from MSDN:

Stored procedures are executed with the SET settings specified during execution with the exception of SET ANSI_NULLS and SET QUOTED_IDENTIFIER. Procedures that specify SET ANSI_NULLS or SET QUOTED_IDENTIFIER are stored, use the one established at the time the stored procedure was created. If used inside a stored procedure, any SET setting is ignored.

So, when creating the stored procedure, SQL Server copies the QUOTED_IDENTIFIER parameter from the connection to the procedure definition. The goal is for someone else with a different QUOTED_IDENTIFIER parameter to still get the behavior of the author of the procedure.

The same does not apply to xact_abort .

Answer 2

You did not indicate whether you use SQL Management Studio or not, but if you click "Edit" in an existing stored procedure (which I assume is what you did), then MS simply generates a template script based on the contents of the existing stored procedure .

You can consider defining stored procedures in a separate script file that executes ALTER PROCEDURE as well as any other parameters that you want to use outside of sproc (for example, SET XACT_ABORT ON). This way you have more control and you can just execute the script to update sproc.