title: Multiple Independent transactions in single stored procedure
body: <p>I have to insert and delete from multiple tables and I wanted to do all these in one single stored procedure so can I have the multiple transactions blocks as below and what would be the best way to handle errors in each transaction.Please advise</p>
  
  <pre><code>      BEGIN TRANSACTION [TRANS1]
       BEGIN TRY
   Insert into  ArchiveTable select * from Completed (nolock) 
  
   delete from Completed(nolock) where convert(date,LastModified,101) &gt; getdate() 
   COMMIT TRANSACTION [TRANS1]
      END TRY
  BEGIN CATCH
   ROLLBACK TRANSACTION [TRANS1]
  PRINT ERROR_MESSAGE() 
   END CATCH 
  </code></pre>
  
  <hr>
  
  <pre><code>     BEGIN TRANSACTION [TRANS2]
       BEGIN TRY
   Insert into  ArchiveTable2 select * from New (nolock) 
  
   delete from New (nolock) where convert(date,LastModified,101) &gt; getdate() 
   COMMIT TRANSACTION [TRANS2]
      END TRY
  BEGIN CATCH
   ROLLBACK TRANSACTION [TRANS2]
  PRINT ERROR_MESSAGE() 
   END CATCH 
  </code></pre>