title: How Should Transactions within a large Stored Procedure be handled?
body: <p>In one of several stored procedures I work with.. things are structured this way:</p>
  
  <pre><code>[begin stored proc]
  BEGIN TRY
  BEGIN TRANSACTION
  
  [dump data to # tables, lots and lots of manipulation]
  [output to display layer]
  
  [UPDATES and INSERTS]
  COMMIT TRANSACTION
  END TRY
  BEGIN CATCH
     ROLLBACK TRANSACTION
     [handle errors]
  END CATCH
  [end stored proc]
  </code></pre>
  
  <p>This "template" of rolling back transactions and handling errors was essentially handed to me by someone else on the team, answering the boss's decree that data modification should not occur in case of any failure in the SP. Now I'm trying to better understand transactions, how to implement them, best practices, etc. This looks like it flies in the face of a lot of the basics I'm reading.</p>
  
  <p>I'm thinking it should look like this: </p>
  
  <pre><code>[begin stored proc]
  BEGIN TRY
  
  [dump data to # tables, lots and lots of manipulation]
  [output to display layer]
  
  BEGIN TRANSACTION
  [UPDATES and INSERTS]
  COMMIT TRANSACTION
  
  END TRY
  BEGIN CATCH
     ROLLBACK TRANSACTION
     [handle errors]
  END CATCH
  [end stored proc]
  </code></pre>
  
  <p>That would work fine for this stored proc, my question is what if we have an SP that is structured like this:</p>
  
  <pre><code>[begin stored proc]
  BEGIN TRY
  
  [dump data to # tables, lots and lots of manipulation]
  [output to display layer]
  
  BEGIN TRANSACTION @one
  [UPDATES and INSERTS]
  COMMIT TRANSACTION @one
  
  [more manipulation based on results of tran @one]
  
  BEGIN TRANSACTION @two
  [UPDATES and INSERTS]
  COMMIT TRANSACTION @two
  
  END TRY
  BEGIN CATCH
     ROLLBACK TRANSACTION [@one? @two?]
     [handle errors]
  END CATCH
  [end stored proc]
  </code></pre>
  
  <p>After @one is committed, there's no rolling it back if any subsequent commands fail, right? I admit I can't imagine a scenario where all the DML couldn't be placed at the end in a single transaction, but then again I'm not that imaginative. In a situation were, in the case of any failure ALL DML must be rolled back, is the only solution to group it all at the end, or wrap the whole thing in a transaction?</p>
  
  <p>(I could be fundamentally misunderstanding something here, if so sorry for the dumb questions)</p>