title: Do you need to call SET XACT_ABORT only when starting a transaction?
body: <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>