Convert VB6.0 code to Stored Procedure

Paging SZLAMANY and others! I wish to convert the following code but I don't know where to start... :(

VB Code:

'Function:
    'To add values to Expenditure field wherein DateEarned field has elapsed for a month,
    'the expenditure value will be deducted from the TotalCost which should be deducted
    'from the Earnings table to reflect the real income for the previous months
Private Sub Command2_Click()
    Dim adoRecordset    As ADODB.Recordset
    Dim adoRecordset2   As ADODB.Recordset
    Dim Total           As Currency
    Dim TotalFrameCost  As Currency
    Dim curExpenditure  As Currency
    Dim curEyeglass     As Currency
    Dim strData         As String
    Dim strSQL          As String
    Dim PurchaseCode    As Long
    
    'Clear previous automated calculations:
    strSQL = "UPDATE tabEarningRecords " & _
             "SET Expenditure = 0 " & _
             "WHERE (Sequence IN " & _
             "(SELECT tabEarningRecords.Sequence " & _
             "FROM tabEGPurchase INNER JOIN " & _
             "tabEGPayments ON tabEGPurchase.Code = tabEGPayments.Code INNER JOIN " & _
             "tabEarningRecords ON tabEGPayments.IncomeCode = tabEarningRecords.Sequence " & _
             "WHERE tabEGPurchase.Code = " & PurchaseCode & "))"
    If ExecuteSQL(strSQL) = True Then
        MsgBox "Ok!"
    Else
        MsgBox "Not Ok!"
    End If
    
    strSQL = "SELECT tabEGPurchase.Code " & _
             "FROM tabEGPurchase INNER JOIN " & _
             "tabEGPayments ON tabEGPurchase.Code = tabEGPayments.Code INNER JOIN " & _
             "tabEarningRecords ON tabEGPayments.IncomeCode = tabEarningRecords.Sequence LEFT OUTER JOIN " & _
             "tabFrameList ON tabEGPurchase.EyeglassCode = tabFrameList.ControlCode " & _
             "WHERE (DateDiff(Month, tabEarningRecords.DateEarned, GETDATE()) > 0) " & _
             "GROUP BY tabEGPurchase.Code"
    Set adoRecordset2 = New ADODB.Recordset
    With adoRecordset2
        .Open strSQL, adoConn, adOpenForwardOnly, adLockReadOnly
        Do While Not .EOF
            'Get purchase code:
            PurchaseCode = .Fields(0)
            
            'Get total cost:
            strSQL = "FROM tabEGPurchase LEFT OUTER JOIN " & _
                     "tabFrameList ON tabEGPurchase.EyeglassCode = tabFrameList.ControlCode " & _
                     "WHERE tabEGPurchase.Code = " & PurchaseCode & ""
            TotalFrameCost = DesiredField("ISNULL(tabFrameList.BuyingPrice, 0) + ISNULL(tabEGPurchase.MTC, 0)", strSQL)
            MsgBox TotalFrameCost, , "TotalCost"
            
            strSQL = "SELECT  tabEarningRecords.EyeGlass, tabEarningRecords.Expenditure " & _
                     "FROM tabEGPurchase INNER JOIN " & _
                     "tabEGPayments ON tabEGPurchase.Code = tabEGPayments.Code INNER JOIN " & _
                     "tabEarningRecords ON tabEGPayments.IncomeCode = tabEarningRecords.Sequence LEFT OUTER JOIN " & _
                     "tabFrameList ON tabEGPurchase.EyeglassCode = tabFrameList.ControlCode " & _
                     "WHERE (DateDiff(Month, tabEarningRecords.DateEarned, GETDATE()) > 0) AND (tabEGPurchase.Code = " & PurchaseCode & ")"
            Set adoRecordset = New ADODB.Recordset
            Total = TotalFrameCost
            
            With adoRecordset
                .Open strSQL, adoConn, adOpenForwardOnly, adLockOptimistic
                Do While Not .EOF
                    curEyeglass = .Fields("Eyeglass")
                    If Total >= curEyeglass Then
                        Total = Total - curEyeglass
                        curExpenditure = curEyeglass
                    Else
                        curExpenditure = Total
                        Total = 0
                    End If
                    .Fields("Expenditure") = curExpenditure
                    .Update
                    strData = "Eyeglass: " & curEyeglass
                    strData = strData & vbNewLine & "Expenditure: " & curExpenditure
                    strData = strData & vbNewLine & "Remaining: " & Total
                    MsgBox strData
                    If Total = 0 Then
                        Exit Do
                    End If
                    .MoveNext
                Loop
                .Close
            End With
            .MoveNext
        Loop
        .Close
    End With
    Set adoRecordset = Nothing
    Set adoRecordset2 = Nothing
End Sub

well...

It seems the first UPDATE can go into a SPROC pretty much exactyl the way it is.

The SPROC needs to receive a PURCHASECODE as a parameter - and that goes into the WHERE clause.

Start the SPROC with BEGIN TRAN and after the UPDATE statement check

If @@Error<>0
Begin
Set @RetText='Not Ok!'
Rollback
Return
End

Make sure that @RetText is a parameter that is defined for OUTPUT.

Now the loop - that's going to be much harder.

I don't understand what the point is of building STRSQL at the top of that other loop - as it's being blown away after the MSGBOX.

But at any rate - you could use a CURSOR to perform that - it's generally not a good idea to use a cursor but sometimes starting with a CURSOR loop will then make it easier to arrive at a SET BASED solution to complete the job.

You will have to start the process and post back with what you have for problems - good luck!

Cursor is what I have thought of at first but was hesitant to proceed since there may be a set-based solution that you could offer me first hand. I'll proceed with the CURSOR solution for now and will come back to post it, thanks for the advice! :afrog:

Here is my effort...

Code:

---

IF EXISTS (SELECT name FROM sysobjects
        WHERE name = 'IncomeCodes' AND type = 'P')
  DROP PROCEDURE IncomeCodes
GO

CREATE PROCEDURE IncomeCodes AS
SET NOCOUNT ON
DECLARE @income_code int, @totalcost money, @eyeglass money, @expenditure money
DECLARE income_cursor CURSOR FOR
        SELECT tabEGPurchase.Code
        FROM tabEGPurchase INNER JOIN
        tabEGPayments ON tabEGPurchase.Code = tabEGPayments.Code INNER JOIN
        tabEarningRecords ON tabEGPayments.IncomeCode = tabEarningRecords.Sequence LEFT OUTER JOIN
        tabFrameList ON tabEGPurchase.EyeglassCode = tabFrameList.ControlCode
        WHERE (DATEDIFF(Month, tabEarningRecords.DateEarned, GETDATE()) > 0)
        GROUP BY tabEGPurchase.Code

UPDATE  tabEarningRecords SET Expenditure = 0
        WHERE (Sequence IN
                (SELECT tabEarningRecords.Sequence
                        FROM tabEGPurchase INNER JOIN
                        tabEGPayments ON tabEGPurchase.Code = tabEGPayments.Code INNER JOIN
                        tabEarningRecords ON tabEGPayments.IncomeCode = tabEarningRecords.Sequence))
       
OPEN income_cursor

FETCH NEXT FROM income_cursor
INTO @income_code

WHILE @@FETCH_STATUS = 0
BEGIN
        SELECT @totalcost=ISNULL(tabFrameList.BuyingPrice, 0) + ISNULL(tabEGPurchase.MTC, 0) FROM tabEGPurchase LEFT OUTER JOIN
        tabFrameList ON tabEGPurchase.EyeglassCode = tabFrameList.ControlCode
        WHERE tabEGPurchase.Code = @income_code

        DECLARE income2_cursor CURSOR FOR
                SELECT  tabEarningRecords.EyeGlass
                FROM tabEGPurchase INNER JOIN
                tabEGPayments ON tabEGPurchase.Code = tabEGPayments.Code INNER JOIN
                tabEarningRecords ON tabEGPayments.IncomeCode = tabEarningRecords.Sequence LEFT OUTER JOIN
                tabFrameList ON tabEGPurchase.EyeglassCode = tabFrameList.ControlCode
                WHERE (DateDiff(Month, tabEarningRecords.DateEarned, GETDATE()) > 0) AND (tabEGPurchase.Code = @income_code)

          OPEN income2_cursor
          FETCH NEXT FROM income2_cursor INTO @eyeglass
       
          WHILE @@FETCH_STATUS = 0
          BEGIN               
                If (@totalcost >= @eyeglass)
                        BEGIN
                                SET @totalcost = @totalcost - @eyeglass
                                SET @expenditure = @eyeglass
                        END
                Else
                        BEGIN
                                SET @expenditure = @totalcost
                                SET @totalcost = 0
                        END
               
                --UPDATE tabEarningRecords SET Expenditure = @expenditure WHERE
                UPDATE tabEarningRecords SET Expenditure = @expenditure
                WHERE CURRENT OF income2_cursor

                PRINT 'eyeglass    : ' + Convert(NVarChar,@eyeglass)
                PRINT 'expenditure : ' + Convert(NVarChar,@expenditure)
                PRINT 'remaining  : ' +  Convert(NVarChar,@totalcost)
                PRINT ''
               
                IF (@totalcost = 0) BREAK

                FETCH NEXT FROM income2_cursor INTO @eyeglass         
          END

  CLOSE income2_cursor
  DEALLOCATE income2_cursor
 
  -- Get the next author.
  FETCH NEXT FROM income_cursor
  INTO @income_code
END

CLOSE income_cursor
DEALLOCATE income_cursor
GO

---

So anyone could help me out converting it to set-based solution? :)