SQL Server Loop through records

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 CURSORS for items unless we can fully explain the reasons why we cannot do this any other way.

Current 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 <> acct2 Then
            Csh = recSet![Due]
        End If

        Csh = Csh+ tranamt
        acct2 = Acct

        recSet.Edit
            recSet![Due] = Csh
        recSet.Update

        recSet.MoveNext
    Loop
End Sub

The record set that is being opened is below:

SELECT ID, EXEC_TM, Due, TAM
FROM FRT
ORDER BY ID, EXEC_DT, EXEC_TM, D_C_CD, SEQ_NBR, TAM DESC

I searc开发者_如何学Gohed through SO and found this. 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.

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.

EDIT:

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

---

Tom's answer is a better one from a process redesign perspective. The code below is a close recreation of your current logic in CURSOR form.

I by no means endorse this method but this is what you asked for. You may need to correct datatypes etc. for it to work correctly.

DECLARE @ID int,
        @Exec_TM int,
        @Due money,
        @TAM money,
        @ID2 int = 0,
        @Cash money


DECLARE RecSet CURSOR FOR

SELECT ID, EXEC_TM, Due, TAM
FROM FRT
ORDER BY ID, EXEC_DT, EXEC_TM, D_C_CD, SEQ_NBR, TAM DESC

OPEN RecSet
FETCH NEXT FROM RecSet INTO @ID, @Exec_TM, @Due, @TAM

WHILE (@@FETCH_STATUS = 0)  
        BEGIN  

            IF @ID <> @ID2 SET @Cash = @Due

            SET @Cash = @Cash + @TAM

            SET @ID2 = @ID

            UPDATE FRT
            SET Due = @Cash
            WHERE ID = @ID
            AND EXEC_TM = @Exec_TM
            AND Due = @Due
            AND TAM = @TAM  

            FETCH NEXT FROM RecSet INTO @ID, @Exec_TM, @Due, @TAM

        END


CLOSE RecSet
DEALLOCATE RecSet

---

First, keeping the "Due" amount in there is a horrible idea from a database design standpoint (and probably from an accounting standpoint).

Second, with your logic if there is a transaction amount on the first row for an account then every time you run this process it will throw off your due amount further and further until you start to owe the customers money.

Here is a basic set-based approach though for calculating running totals:

SELECT
    L1.account_id,
    L1.transaction_date,
    L1.amt,
    SUM(L2.amt)
FROM
    Ledger L1
INNER JOIN Ledger L2 ON
    L2.account_id = L1.account_id AND
    L2.transaction_date <= L1.transaction_date
GROUP BY
    L1.account_id,
    L1.transaction_date,
    L1.amt

Because you have that "due" column separate, it will cause problems here because it looks like you're only counting that for the first entry. You can account for that in the query above, but it will involve either a subquery or additional joins.