SQL Server Loop through records

Question

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 searched 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

Is it a typo that you have both `Cash` and `Csh` in the script? — JNK, Jul 08 '11 at 17:45
Am I correct in guessing you are getting a rolling total for each ID? — JNK, Jul 08 '11 at 17:55
Yes, it gets a rolling total for each ID. And the new total gets updated for each EXEC_TM. I added sample data of before and after in the current process — Taryn, Jul 08 '11 at 18:00
[This question](http://stackoverflow.com/questions/5840857/sql-running-subtraction) has to do with running subtraction, similar to your problem. — Andriy M, Jul 08 '11 at 20:24

score 7 · Accepted Answer · answered Jul 08 '11 at 18:11

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

I got approval to use a CURSOR so thanks for this. It works as expected. — Taryn, Jul 11 '11 at 14:17

Tom H · Answer 2 · 2011-07-12T18:07:25.527

3

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.

edited Jul 12 '11 at 18:07

answered Jul 08 '11 at 18:02

Tom H

46,766
14
87
128

Basically what I need to do is find a running total of the transactions that were performed for the day. This is used to possibly flag an account. The Due column is strictly being used for the processing of the data. – Taryn Jul 08 '11 at 18:07
I'm still not entirely clear. Does the `due` column contain values when you run your process? Also, if you're only looking at one day do you care what the running total for that account was at the start of the day? – Tom H Jul 08 '11 at 18:36
Yes, the Due column contains data at the start of the process. I added some sample data to my original question. I start with the initial Due value and then add the TAM amount, this final value is then used to update the original Due amount. Then the now calculated Due amount is the starting point for the next transaction. – Taryn Jul 08 '11 at 18:42
Right... so if you run this twice, you'll update the due column twice and it will throw your numbers off. – Tom H Jul 08 '11 at 18:46
Where is the T2 table alias coming from in the SUM(T2.amt) line? – mg1075 Jul 12 '11 at 17:59
To be more precise: where are the T1 and T2 table aliases coming from; did you mean those to be L1 and L2 respectively? – mg1075 Jul 12 '11 at 18:06
That's coming from when I changed my table names/aliases and didn't fix it in all spots :) I'll fix it now, thanks. – Tom H Jul 12 '11 at 18:06
Cool, thanks. I like how you emphasize set-based solutions for SQL rather than loops. I think it's trickier to understand, but definitely can outperform loops when dealing with large datasets. – mg1075 Jul 12 '11 at 18:10

SQL Server Loop through records

2 Answers2