title: How to rollback and stop execution from an inner stored procedure
body: <p>Let's say I have two stored procedures, <code>Outer</code> and <code>Inner</code>:</p>
  
  <pre><code>CREATE PROCEDURE dbo.Outer
  AS
  BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
  
    BEGIN TRAN
      EXEC Inner
  
      -- Perform additional processing (which should not occur if there is 
      -- a ROLLBACK in Inner)
      ...
    COMMIT
  END;
  GO
  </code></pre>
  
  <p>The <code>Outer</code> stored procedure turns on XACT_ABORT and starts an explicit transaction. It then calls the <code>Inner</code> stored procedure inside the transaction.</p>
  
  <pre><code>CREATE PROCEDURE dbo.Inner
  AS
  BEGIN
    DECLARE @Id INT=(SELECT Id FROM SomeTable WHERE ...);
  
    IF (@Id IS NOT NULL)
      ROLLBACK;
  
    INSERT INTO SomeTable(...)
    VALUES (...);
  END;
  GO
  </code></pre>
  
  <p>The <code>Inner</code> stored procedure performs a check to see if something is present and if it is wants to rollback the entire transaction started in the <code>Outer</code> stored procedure and abort all further processing in both <code>Inner</code> and <code>Outer</code>.</p>
  
  <p>The thing that happens instead of what I expect as outlined above, is that I get the error message:</p>
  
  <p>In Inner:</p>
  
  <blockquote>
    <p>Transaction count after EXECUTE indicates a mismatching number of
    BEGIN and COMMIT statements. Previous count = 1, current count = 0.</p>
  </blockquote>
  
  <p>In Outer:</p>
  
  <blockquote>
    <p>The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.</p>
  </blockquote>
  
  <p>Clearly, the <code>ROLLBACK</code> even with XACT_ABORT turned on does not stop the flow of execution. Putting a <code>RETURN</code> statement after the <code>ROLLBACK</code> gets us out of <code>Inner</code>, but execution in <code>Outer</code> continues. What do I need to do in order to cause the <code>ROLLBACK</code> to stop all further processing and effectively cause an exit out of <code>Outer</code>?</p>
  
  <p>Thanks for any help.</p>