4

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?

rem
  • 16,745
  • 37
  • 112
  • 180
  • You haven't included the `SET` statement in the procedure definition. As you are on 2008 you should look into `TRY ... CATCH` though. – Martin Smith Nov 05 '11 at 21:03
  • @Martin Smith: Do you mean not to include `SET XACT_ABORT ON` at all, but rather catch an error by `TRY..CATCH` and explicitly `ROLLBACK` a transaction? – rem Nov 06 '11 at 11:15
  • See [this answer](http://stackoverflow.com/questions/1150032/what-is-the-benefit-of-using-set-xact-abort-on-in-a-stored-procedure/1150350#1150350) and comments underneath for a good discussion about this. I definitely wouldn't bother with testing `@@ERROR` on SQL Server 2008 as your code is doing when `TRY...CATCH` is available. Just to mention `XACT_ABORT` has a different effect in a `TRY` block. – Martin Smith Nov 06 '11 at 11:20
  • @Martin Smith: Thanks, Martin! I'll pay attention to it. – rem Nov 06 '11 at 11:49

2 Answers2

7

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.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • Why does your sql example use `SET XACT_ABORT ON` and also uses a transaction? Isn't the purpose of setting `XACT_ABORT` to `ON` to make the whole procedure atomic? Why do you still need transaction? – James Wierzba Jun 14 '17 at 19:56
  • Ok I see that the author already had that in his code sample. Is my previous statement accurate, though? – James Wierzba Jun 14 '17 at 19:59
0

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.

Dan
  • 114
  • 2
  • 9