title: Multiple transactions in a single stored procedure
body: <p>I need to delete data from 2 tables in SQL Server 2008.</p>
  
  <p>I have two tables A and B. I need to put each in a separate transaction.</p>
  
  <p>This is the code I am using.</p>
  
  <pre><code>BEGIN TRANSACTION;
  BEGIN TRY
      DELETE      from A
  END TRY
  BEGIN CATCH
      IF @@TRANCOUNT &gt; 0
          ROLLBACK TRANSACTION;
  
  END CATCH
  
  IF @@TRANCOUNT &gt; 0
      COMMIT TRANSACTION;
  
  
  BEGIN TRANSACTION;
  BEGIN TRY
      DELETE     from B
  END TRY
  BEGIN CATCH
      IF @@TRANCOUNT &gt; 0
          ROLLBACK TRANSACTION;
  END CATCH
  
  IF @@TRANCOUNT &gt; 0
      COMMIT TRANSACTION;
  </code></pre>
  
  <p>Is there any better way to implement multiple transactions and error handling in SQL Server?</p>
  
  <p>I need to put separate transactions for each table.</p>
  
  <p>I am getting an error when one of the transactions is failed.</p>
  
  <blockquote>
    <p><em>Transaction count after EXECUTE indicates a mismatching number of
    BEGIN and COMMIT statements. the records are not deleting from other
    transaction</em></p>
  </blockquote>