0

I have a stored procedure nested inside another stored procedure. Sould I SET XACT ABORT ON in both stored procedures or is it sufficient to set this in the calling stored procedure? Both stored procedures have their own nested transactions.

w0051977
  • 15,099
  • 32
  • 152
  • 329
  • I don't know your scenario. I would like to prefer to SET ON. I think if more save and easy to understand once you have problem. easy to investigate the problem. – Pongsathon.keng Feb 15 '12 at 10:00
  • In both for consistency + I'll assume that each proc can be called independently. And for nested procs, see http://stackoverflow.com/questions/2073737/nested-stored-procedures-containing-try-catch-rollback-pattern/2074139#2074139 – gbn Feb 15 '12 at 10:01
  • Also, possible dupe of that link above or http://stackoverflow.com/questions/917773/do-i-really-need-to-use-set-xact-abort-on/919279#919279 – gbn Feb 15 '12 at 10:02
  • @gbn - Thanks for the links to the two posts, but I don't think either talk specifically about nested stored procedures i.e. a stored procedure called within a stored procedure. – w0051977 Feb 15 '12 at 11:24
  • 1
    @w0051977: yes, the first link does. It's my answer – gbn Feb 15 '12 at 12:05
  • @gbn - thanks. Could you confirm whether XACT ABORT ON should be set in both the calling stored procedure AND the called stored procedure? Also if there is an error in the stored procedure when XACT ABORT ON, then how does the client know? Traditionally I have set an error flag (output variable) in the CATCH clause. – w0051977 Feb 15 '12 at 19:50
  • @gbn, you say: "The rollback check is actually redundant because of SET XACT_ABORT ON". In that case, how does the client know that there is an error? – w0051977 Feb 15 '12 at 20:50
  • SET XACT_ABORT ON will not stop the propagation of the error to the caller. So, no worries. – Pittsburgh DBA Feb 20 '12 at 21:16

0 Answers0