[dba-SQLServer]Do Loop in a sproc

UPDATE tblPrintCharges set ExceptionCode = left(ExceptionCode,len(ExceptionCode) - 1) WHERE charindex(',', ltrim(reverse(ExceptionCode))) > 0
 
 
-----Original Message-----
From: Chris McIsaac [mailto:cjm at haleyaldrich.com]
Sent: Wednesday, February 12, 2003 9:41 AM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer]Do Loop in a sproc
 
This is what I was afraid of.  In TSQL, there is something called a cursor that allows you move through a set record by record, 
but the performance is horrible.  A better way...
 
 UPDATE tblPrintCharges set ExceptionCode = ExceptionCode + "18," where EndServDate = '' 
 UPDATE tblPrintCharges set ExceptionCode = ExceptionCode + "19," where CPT = ''
 etc.
 

 -- trim the final comma
 UPDATE tblPrintCharges set ExceptionCode = left(ExceptionCode,len(ExceptionCode) - 1) WHERE ExceptionCode IS NOT NULL AND ExceptionCode <> ''
 
-----Original Message-----
From: Mark Boyd [mailto:MarkBoyd at mcbeeassociates.com] 
Sent: 12 February 2003 10:18 AM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [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/a762114c/attachment.html>