Convert VB6.0 code to Stored Procedure

[dee-u]

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

'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

[szlamany]

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!

[dee-u]

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!

[dee-u]

Here is my effort...

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?