title: SAVE TRANSACTION vs BEGIN TRANSACTION (SQL Server) how to nest transactions nicely
body: <p>I have a stored procedure that needs to set a save point so that it can, under certain circumstances, undo everything it did and return an error code to the caller, or accept/commit it and return success to the caller.  But I need it to work whether the caller has already started a transaction or not.  The doc is extremely confusing on this subject.  Here is what I think will work, but I'm not certain of all the ramifications.</p>
  
  <p>The thing is - this <code>Stored Procedure (SP)</code> is called by others.  So I don't know if they've started a transaction or not...  Even if I require users to start a transaction to use my SP, I still have questions about the proper use of <code>Save Points</code> ...</p>
  
  <p>My SP will test if a transaction is in progress, and if not, start one with <code>BEGIN TRANSACTION</code>.  If a transaction is already in progress, it will instead create a save point with <code>SAVE TRANSACTION MySavePointName</code>, and save the fact this is what I did.</p>
  
  <p>Then if I have to roll back my changes, if I did a <code>BEGIN TRANSACTION</code> earlier, then I will <code>ROLLBACK TRANSACTION</code>.  If I did the save point, then I will <code>ROLLBACK TRANSACTION MySavePointName</code>.  This scenario seems to work great.</p>
  
  <p>Here is where I get a little confused - if I want to keep the work I've done, if I started a transaction I will execute <code>COMMIT TRANSACTION</code>. But if I created a save point?  I tried <code>COMMIT TRANSACTION MySavePointName</code>, but then the caller tries to commit its transaction and gets an error: </p>
  
  <blockquote>
    <p>The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.</p>
  </blockquote>
  
  <p>So I'm wondering then - a save point can be rolled back (that works: <code>ROLLBACK TRANSACTION MySavePointName</code> will NOT roll back the caller's transaction).  But perhaps one never needs to "commit" it?  It just stays there, in case you need to roll back to it, but goes away once the original transaction is committed (or rolled back)?</p>
  
  <p>If there is a "better" way to "nest" a transaction, please shed some light as well.  I haven't figured out how to nest with <code>BEGIN TRANSACTION</code> but only rollback or commit my internal transaction.  Seems <code>ROLLBACK</code> will always roll back to the top transaction, while <code>COMMIT</code> simply decrements <code>@@trancount</code>.</p>