CODEWITHSUNDEEP
sql-servert-sqlstored-procedureserror-handlingtransactions
Kenny
Kenny

Reputation: 1982

SET XACT_ABORT OFF if transaction terminated by SET XACT_ABORT ON

I read that many tend to SET XACT_ABORT ON at the beginning of the procedure.

CREATE PROC myProc
AS
Begin
BEGIN TRAN
 SET XACT_ABORT ON
 [..code1 that might throw an error..]
 [..code2..]
 SET XACT_ABORT OFF [?]
COMMIT TRAN
END
SET XACT_ABORT OFF [?]

Because run-time errors will terminate the procedure, SET XACT_ABORT will be left as ON. I have some questions :

  1. Where do you set it on ? Before the CREATE PROC definition, after BEGIN or after BEGIN TRAN ? SET is at connection level so I suppose all three would not make a difference ?

  2. When do you turn it OFF if it is left ON when errors occur ?

Upvotes: 0

Views: 713

Answers (1)

Razvan Socol
Razvan Socol

Reputation: 5684

Setting XACT_ABORT before the CREATE PROC definition doesn't have much sense. Unlike ANSI_NULLS or QUOTED_IDENTIFIER, this option is not stored as a property of the stored procedure. I would say that you should set XACT_ABORT immediately after CREATE PROC myProc AS or after the first BEGIN, where you would also place the SET NOCOUNT ON.

I would not bother setting XACT_ABORT off. If any code portion needs to have it off (for example, inside a TRY-CATCH block that ignores some errors), it should set it off inside that specific TRY block, and set it back on at the end of END CATCH.

For more info, see: http://www.sommarskog.se/error_handling/Part1.html

Upvotes: 1

Related Questions