[AccessD] Creating a stored procedure

Arthur's task list for porting such code to sprocs:

1. Identify each recordset and create a corresponding view or UDF that returns the identical result set.
2. Create one or more SELECT statements that join these views|UDFs the way you need them joined. now you have some reusable components.
3. Combine these reusable components in a SELECT statement that gives you all the rows you need and only those rows. Save this to a new view or UDF.
4. Consider passing parameters that are defaulted. The way you've written the code, you walk the entire outermost table (tblClaims) without investigating whether or not there exist child rows. In a stored procedure, you can specify a parameter and give it a default value -- then react to its value within your sproc code. For example, your sproc might accept a parameter @ClaimNo whose default is zero; in the event that @ClaimNo = 0, process all Claims; else process only the specified ClaimNo. This will result in dramatically faster execution in most situations. 
5. To make all this work from an ADP, you will need to investigate command and parameter objects. This stuff is not difficult, but if you need some help I will supply some sample code.

At the end of the day, you're updating a few rows in one table. When you move from recordset code to resultset code, the first thing you should kiss goodbye is the notion of a row. (I realize that is a very general statement, but its exceptions are few and typically reflect a bad database design rather than a need to sharpen set code to row code.)

Just my $.02.
A.
  ----- Original Message ----- 
  From: Mark Boyd 
  To: AccessD at databaseadvisors.com 
  Sent: Monday, February 03, 2003 9:39 AM
  Subject: [AccessD] Creating a stored procedure


  Cross-posted to SQLServer list.



  Let me start out by stating that I am totally novice to creating stored procedures.

  I hope someone can help me to create my first stored proc.



  I have the VBA code complete, but am having trouble converting to the proc.

  The code is below:



  Set rst = db.OpenRecordset("SELECT * FROM tblClaim ORDER BY ClaimNo", dbOpenDynaset, dbSeeChanges)

  rst.MoveFirst

  Do Until rst.EOF = True

        Me.txtStatus = "Processing record " & rst!PatientNo

        Me.Repaint

            

        curPaidAmt = 0

        curCharge = 0

        intClaimNo = rst!ClaimNo

        intReferral = Nz(rst!Referral)

        strProvNo = rst!ProvNo

        Set rstPrint = db.OpenRecordset("SELECT * FROM tblPrintCharges WHERE ClaimNo = " & intClaimNo & "")

        If rstPrint.EOF = False Then

            rstPrint.MoveFirst

            Do Until rstPrint.EOF = True

                rst.Edit

                rst!PaidAmt = Nz(rst!PaidAmt) + Nz(rstPrint!PaidAmt)

                curPaidAmt = Nz(curPaidAmt) + Nz(rstPrint!PaidAmt)

                rst!TotalClaimAmt = Nz(rst!TotalClaimAmt) + Nz(rstPrint!Charge)

                curCharge = Nz(curCharge) + Nz(rstPrint!Charge)

                rst.Update

                rstPrint.MoveNext

            Loop

        End If

              

        Set rstPhy = db.OpenRecordset("SELECT * FROM tblCodPhy WHERE Referral = " & intReferral & " AND ProvNo = '" & strProvNo & "'")

        If rstPhy.EOF = False Then

            rstPhy.MoveFirst

            Do Until rstPhy.EOF = True

                rstPhy.Edit

                rstPhy!ClaimPaid = "CP"

                rstPhy!ClaimPaidDate = Int(Now())

                rstPhy!ClaimPaidAmt = Nz(rstPhy!ClaimPaidAmt) + Nz(curPaidAmt)

                rstPhy!ClaimChargeAmt = Nz(rstPhy!ClaimChargeAmt) + Nz(curCharge)

                rstPhy.Update

                rstPhy.MoveNext

            Loop

        End If

              

        curPaidAmt = 0

        curCharge = 0

        curTotalCharges = 0

        'Get total claim charge amounts per referral

        Set rstPhy = db.OpenRecordset("SELECT * FROM tblCodPhy WHERE Referral = " & intReferral & "")

        If rstPhy.EOF = False Then

            rstPhy.MoveFirst

            Do Until rstPhy.EOF = True

                curTotalCharges = Nz(curTotalCharges) + Nz(rstPhy!ClaimChargeAmt)

                rstPhy.MoveNext

            Loop

        End If

              

        DoCmd.RunSQL ("UPDATE tblCod SET CaseCost = UBTotal + " & curTotalCharges & " WHERE Referral = " & intReferral & "")

        rst.MoveNext

    Loop



  I'm not exactly sure which sections to include in the proc.  Should I include the whole thing?  

  How do I create a Do Loop in the proc?



  Any help/direction is greatly appreciated.

  Thanks,

  Mark Boyd

  Sr. Systems Analyst

  McBee Associates, Inc


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030203/7895c0e5/attachment-0002.html>