title: SQL Server Loop through records
body: <p>I have been online looking for the best way to rewrite this current VBA/MS Access Sub to work in a stored procedure in SQL server 2005.   We are not supposed to use <code>CURSORS</code> for items unless we can fully explain the reasons why we cannot do this any other way.  </p>
  
  <p>Current Code:</p>
  
  <pre><code>Public Sub updcsh()
  
  Dim tranamt
  Dim Acct
  Dim acct2
  Dim Csh
  Dim recSet As Recordset
  
      DAO.DBEngine.SetOption dbMaxLocksPerFile, 100000
  
      Set recSet = CurrentDb.OpenRecordset("FndVios")
      recSet.MoveFirst
  
  
      Do Until recSet.EOF
          Acct = recSet![ID]
          tranamt = recSet![TAM]
  
          If Acct &lt;&gt; acct2 Then
              Csh = recSet![Due]
          End If
  
          Csh = Csh+ tranamt
          acct2 = Acct
  
          recSet.Edit
              recSet![Due] = Csh
          recSet.Update
  
          recSet.MoveNext
      Loop
  End Sub
  </code></pre>
  
  <p>The record set that is being opened is below:</p>
  
  <pre><code>SELECT ID, EXEC_TM, Due, TAM
  FROM FRT
  ORDER BY ID, EXEC_DT, EXEC_TM, D_C_CD, SEQ_NBR, TAM DESC
  </code></pre>
  
  <p>I searched through SO and found <a href="https://stackoverflow.com/questions/61967/is-there-a-way-to-loop-through-a-table-variable-in-tsql-without-using-a-cursor">this</a>. I looked at cursors and other methods on how to write this for a stored proc and I cannot figure out the correct way to rewrite this.  </p>
  
  <p>Can anyone offer up any suggestions?  Should I use a temp table?  I have not done this before and I am stumped at how to proceed.</p>
  
  <p>EDIT:</p>
  
  <pre><code>Sample Data
  Original:
  
  ID         EXEC_TM    Due        TAM
  12345678   12343811   $9250.81   $-6561.91
  12345678   12343822   $9250.81   $-4374.63
  12345678   15581917   $9250.81   $-4762.76
  
  Final Result:
  
  ID         EXEC_TM    Due         TAM
  12345678   12343811   $2688.87    $-6561.91
  12345678   12343822   $-1685.76   $-4374.63
  12345678   15581917   $-6448.52   $-4762.76
  </code></pre>