Databases
StackOverflow2013
Postsdbo
PODo you need to call SET XACT_ABORT only when starting a transaction?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODo you need to call SET XACT_ABORT only when starting a transaction?
    primarykey
    data
    text
    <p>I would like to get some clarification to this answer -> <a href="https://stackoverflow.com/questions/2073737/nested-stored-procedures-containing-try-catch-rollback-pattern/2074139#2074139">Nested stored procedures containing TRY CATCH ROLLBACK pattern?</a></p> <p>Below is the code sample from the above link</p> <pre><code> 1 CREATE PROCEDURE [Name] 2 AS 3 SET XACT_ABORT, NOCOUNT ON 4 5 DECLARE @starttrancount int 6 7 BEGIN TRY 8 SELECT @starttrancount = @@TRANCOUNT 9 10 IF @starttrancount = 0 11 BEGIN TRANSACTION 12 13 [...Perform work, call nested procedures...] 14 15 IF @starttrancount = 0 16 COMMIT TRANSACTION 17 END TRY 19 BEGIN CATCH 20 IF XACT_STATE() &lt;&gt; 0 AND @starttrancount = 0 21 ROLLBACK TRANSACTION 22 RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc] 23 END CATCH 24 GO </code></pre> <p>Suppose it is called from the legacy stored procedure that starts a transaction. In this case this proc will not start transaction of its own, but will alter the XACT_ABORT state for the call.</p> <p>So, I have a few questions here.</p> <ul> <li>Is XACT_ABORT active for the current proc only, or for the whole call stack?</li> <li>If I want to refactor a proc to use SET XACT_ABORT ON, do I need to pair it up with SET XACT_ABORT OFF? Is this the safest way to do it for legacy code?</li> </ul> <p>Below is the modified sample that turns on XACT_ABORT conditionally and pairs it up with turning it off on a proc exit</p> <pre><code>CREATE PROCEDURE [Name] AS SET NOCOUNT ON DECLARE @starttrancount int BEGIN TRY SELECT @starttrancount = @@TRANCOUNT IF @starttrancount = 0 BEGIN SET XACT_ABORT ON BEGIN TRANSACTION END [...Perform work, call nested procedures...] IF @starttrancount = 0 BEGIN COMMIT TRANSACTION SET XACT_ABORT OFF END END TRY BEGIN CATCH IF XACT_STATE() &lt;&gt; 0 AND @starttrancount = 0 BEGIN ROLLBACK TRANSACTION SET XACT_ABORT OFF END RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc] END CATCH GO </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USAlexander Pogrebnyak
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PODo you need to call SET XACT_ABORT only when starting a transaction?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PODo you need to call SET XACT_ABORT only when starting a transaction?
      1. USMartin Smith
      1. VTFavorite
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload