[dba-SQLServer]Do Loop in a sproc

Chris -

The code is as follows.

It was written by another programmer about 7 years ago.

I'm sure there is a better way to code this, but it needs to be finished
by the end of the day.

 

Set db = CurrentDb()

Set rstPrint = db.OpenRecordset("SELECT * FROM tblPrintCharges")

    If rstPrint.EOF = False Then

        rstPrint.MoveFirst

        Do Until rstPrint.EOF = True

            Me.txtStatus = "Processing record '" & rstPrint!PatientNo &
"'"

            Me.Repaint

            If Nz(rstPrint!EndServDate) = "" Then

                rstPrint.Edit

                rstPrint!ExceptionCode = rstPrint!ExceptionCode & "18,"

                rstPrint.Update

            End If

            If Nz(rstPrint!CPT) = "" Then

                rstPrint.Edit

                rstPrint!ExceptionCode = rstPrint!ExceptionCode & "19,"

                rstPrint.Update

            End If

            If Nz(rstPrint!BegServDate) = "" Then

                rstPrint.Edit

                rstPrint!ExceptionCode = rstPrint!ExceptionCode & "20,"

                rstPrint.Update

            End If

            If Nz(rstPrint!Charge) = "" Then

                rstPrint.Edit

                rstPrint!ExceptionCode = rstPrint!ExceptionCode & "23,"

                rstPrint.Update

            End If

            rstPrint.MoveNext

        Loop

    Else

        MsgBox "There are no outstanding charges.", vbCritical, "No
Charges"

    End If

 

If I use the format you suggest, how do I specify the WHILE (condition =
true) without setting the recordset?

Can I say WHILE tblPrintCharges.EOF = False ... ?

 

Thanks,

Mark

 

-----Original Message-----
From: Chris McIsaac [mailto:cjm at haleyaldrich.com] 
Sent: Wednesday, February 12, 2003 9:38 AM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer]Do Loop in a sproc

 

The format is...

 

WHILE (condition = true)

BEGIN

    -- do processing

    

END

 

Keep in mind that a direct translation of programming logic from
whatever to TSql may not be the best way to go.  If you post what you
are trying to do, people may be able to give you an alternative to using
a loop (which tend to be much slower than set-based statements).

 

	-----Original Message-----
	From: Mark Boyd [mailto:MarkBoyd at mcbeeassociates.com] 
	Sent: 12 February 2003 9:26 AM
	To: SQLServerList
	Subject: [dba-SQLServer]Do Loop in a sproc

	I am converting VBA code to a stored procedure.

	This code includes a pretty large Do Loop.

	Is it possible to create a loop in a sproc?

	If so, what is the format?

	 

	Thanks,

	Mark Boyd

	Sr. Systems Analyst

	McBee Associates, Inc.

	 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/dba-sqlserver/attachments/20030212/7a645aaa/attachment.html>