[dba-SQLServer]Do Loop in a sproc

Chris -

I agree with your recommendation about avoiding a loop.

I think creating a sproc with multiple UPDATE queries is definitely the
way to go.

 

Thanks for the insight,

Mark

 

-----Original Message-----
From: Chris McIsaac [mailto:cjm at haleyaldrich.com] 
Sent: Wednesday, February 12, 2003 10: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/e5630e3c/attachment.html>