title: Transactions within loop within stored procedure
body: <p>I'm working on a procedure that will update a large number of items on a remote server, using records from a local database.  Here's the pseudocode.</p>
  
  <pre><code>CREATE PROCEDURE UpdateRemoteServer
      pre-processing
      get cursor with ID's of records to be updated
      while on cursor
          process the item
  </code></pre>
  
  <p>No matter how much we optimize it, the routine is going to take a while, so we don't want the whole thing to be processed as a single transaction.  The items are flagged after being processed, so it should be possible to pick up where we left off if the process is interrupted.</p>
  
  <p>Wrapping the contents of the loop ("process the item") in a begin/commit tran does not do the trick... it seems that the whole statement</p>
  
  <pre><code>EXEC UpdateRemoteServer
  </code></pre>
  
  <p>is treated as a single transaction.  How can I make each item process as a complete, separate transaction?</p>
  
  <p>Note that I would love to run these as "non-transacted updates", but that option is only available (so far as I know) in 2008.</p>