<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 10 (filtered)">
<title>Message</title>
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
p.MsoAutoSig, li.MsoAutoSig, div.MsoAutoSig
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
span.emailstyle17
{font-family:Tahoma;
color:windowtext;
font-weight:normal;
font-style:normal;
text-decoration:none none;}
span.EmailStyle19
{font-family:Tahoma;
color:blue;
font-weight:normal;
font-style:normal;
text-decoration:none none;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><font size=2 color=blue face=Tahoma><span style='font-size:
10.0pt;font-family:Tahoma;color:blue'>Chris –</span></font></p>
<p class=MsoNormal><font size=2 color=blue face=Tahoma><span style='font-size:
10.0pt;font-family:Tahoma;color:blue'>The code is as follows.</span></font></p>
<p class=MsoNormal><font size=2 color=blue face=Tahoma><span style='font-size:
10.0pt;font-family:Tahoma;color:blue'>It was written by another programmer about
7 years ago.</span></font></p>
<p class=MsoNormal><font size=2 color=blue face=Tahoma><span style='font-size:
10.0pt;font-family:Tahoma;color:blue'>I’m sure there is a better way to
code this, but it needs to be finished by the end of the day.</span></font></p>
<p class=MsoNormal><font size=2 color=blue face=Tahoma><span style='font-size:
10.0pt;font-family:Tahoma;color:blue'> </span></font></p>
<p class=MsoNormal><font size=1 color=blue face=Tahoma><span style='font-size:
8.0pt;font-family:Tahoma;color:blue'>Set db = CurrentDb()</span></font></p>
<p class=MsoNormal><font size=1 color=blue face=Tahoma><span style='font-size:
8.0pt;font-family:Tahoma;color:blue'>Set rstPrint = db.OpenRecordset("SELECT
* FROM tblPrintCharges")</span></font></p>
<p class=MsoNormal><font size=1 color=blue face=Tahoma><span style='font-size:
8.0pt;font-family:Tahoma;color:blue'> If rstPrint.EOF = False
Then</span></font></p>
<p class=MsoNormal><font size=1 color=blue face=Tahoma><span style='font-size:
8.0pt;font-family:Tahoma;color:blue'>
rstPrint.MoveFirst</span></font></p>
<p class=MsoNormal><font size=1 color=blue face=Tahoma><span style='font-size:
8.0pt;font-family:Tahoma;color:blue'>
Do Until rstPrint.EOF = True</span></font></p>
<p class=MsoNormal><font size=1 color=blue face=Tahoma><span style='font-size:
8.0pt;font-family:Tahoma;color:blue'>
Me.txtStatus = "Processing record '" & rstPrint!PatientNo &
"'"</span></font></p>
<p class=MsoNormal><font size=1 color=blue face=Tahoma><span style='font-size:
8.0pt;font-family:Tahoma;color:blue'>
Me.Repaint</span></font></p>
<p class=MsoNormal><font size=1 color=blue face=Tahoma><span style='font-size:
8.0pt;font-family:Tahoma;color:blue'>
If Nz(rstPrint!EndServDate) = "" Then</span></font></p>
<p class=MsoNormal><font size=1 color=blue face=Tahoma><span style='font-size:
8.0pt;font-family:Tahoma;color:blue'>
rstPrint.Edit</span></font></p>
<p class=MsoNormal><font size=1 color=blue face=Tahoma><span style='font-size:
8.0pt;font-family:Tahoma;color:blue'>
rstPrint!ExceptionCode = rstPrint!ExceptionCode & "18,"</span></font></p>
<p class=MsoNormal><font size=1 color=blue face=Tahoma><span style='font-size:
8.0pt;font-family:Tahoma;color:blue'>
rstPrint.Update</span></font></p>
<p class=MsoNormal><font size=1 color=blue face=Tahoma><span style='font-size:
8.0pt;font-family:Tahoma;color:blue'>
End If</span></font></p>
<p class=MsoNormal><font size=1 color=blue face=Tahoma><span style='font-size:
8.0pt;font-family:Tahoma;color:blue'>
If Nz(rstPrint!CPT) = "" Then</span></font></p>
<p class=MsoNormal><font size=1 color=blue face=Tahoma><span style='font-size:
8.0pt;font-family:Tahoma;color:blue'>
rstPrint.Edit</span></font></p>
<p class=MsoNormal><font size=1 color=blue face=Tahoma><span style='font-size:
8.0pt;font-family:Tahoma;color:blue'>
rstPrint!ExceptionCode = rstPrint!ExceptionCode & "19,"</span></font></p>
<p class=MsoNormal><font size=1 color=blue face=Tahoma><span style='font-size:
8.0pt;font-family:Tahoma;color:blue'>
rstPrint.Update</span></font></p>
<p class=MsoNormal><font size=1 color=blue face=Tahoma><span style='font-size:
8.0pt;font-family:Tahoma;color:blue'>
End If</span></font></p>
<p class=MsoNormal><font size=1 color=blue face=Tahoma><span style='font-size:
8.0pt;font-family:Tahoma;color:blue'>
If Nz(rstPrint!BegServDate) = "" Then</span></font></p>
<p class=MsoNormal><font size=1 color=blue face=Tahoma><span style='font-size:
8.0pt;font-family:Tahoma;color:blue'>
rstPrint.Edit</span></font></p>
<p class=MsoNormal><font size=1 color=blue face=Tahoma><span style='font-size:
8.0pt;font-family:Tahoma;color:blue'>
rstPrint!ExceptionCode = rstPrint!ExceptionCode & "20,"</span></font></p>
<p class=MsoNormal><font size=1 color=blue face=Tahoma><span style='font-size:
8.0pt;font-family:Tahoma;color:blue'>
rstPrint.Update</span></font></p>
<p class=MsoNormal><font size=1 color=blue face=Tahoma><span style='font-size:
8.0pt;font-family:Tahoma;color:blue'>
End If</span></font></p>
<p class=MsoNormal><font size=1 color=blue face=Tahoma><span style='font-size:
8.0pt;font-family:Tahoma;color:blue'>
If Nz(rstPrint!Charge) = "" Then</span></font></p>
<p class=MsoNormal><font size=1 color=blue face=Tahoma><span style='font-size:
8.0pt;font-family:Tahoma;color:blue'>
rstPrint.Edit</span></font></p>
<p class=MsoNormal><font size=1 color=blue face=Tahoma><span style='font-size:
8.0pt;font-family:Tahoma;color:blue'>
rstPrint!ExceptionCode = rstPrint!ExceptionCode & "23,"</span></font></p>
<p class=MsoNormal><font size=1 color=blue face=Tahoma><span style='font-size:
8.0pt;font-family:Tahoma;color:blue'>
rstPrint.Update</span></font></p>
<p class=MsoNormal><font size=1 color=blue face=Tahoma><span style='font-size:
8.0pt;font-family:Tahoma;color:blue'>
End If</span></font></p>
<p class=MsoNormal><font size=1 color=blue face=Tahoma><span style='font-size:
8.0pt;font-family:Tahoma;color:blue'>
rstPrint.MoveNext</span></font></p>
<p class=MsoNormal><font size=1 color=blue face=Tahoma><span style='font-size:
8.0pt;font-family:Tahoma;color:blue'>
</span></font><font size=1 color=blue face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma;color:blue'>Loop</span></font></p>
<p class=MsoNormal><font size=1 color=blue face=Tahoma><span style='font-size:
8.0pt;font-family:Tahoma;color:blue'> Else</span></font></p>
<p class=MsoNormal><font size=1 color=blue face=Tahoma><span style='font-size:
8.0pt;font-family:Tahoma;color:blue'>
MsgBox "There are no outstanding charges.", vbCritical, "No
Charges"</span></font></p>
<p class=MsoNormal><font size=1 color=blue face=Tahoma><span style='font-size:
8.0pt;font-family:Tahoma;color:blue'> End If</span></font></p>
<p class=MsoNormal><font size=2 color=blue face=Tahoma><span style='font-size:
10.0pt;font-family:Tahoma;color:blue'> </span></font></p>
<p class=MsoNormal><font size=2 color=blue face=Tahoma><span style='font-size:
10.0pt;font-family:Tahoma;color:blue'>If I use the format you suggest, how do I
specify the WHILE (condition = true) without setting the recordset?</span></font></p>
<p class=MsoNormal><font size=2 color=blue face=Tahoma><span style='font-size:
10.0pt;font-family:Tahoma;color:blue'>Can I say WHILE tblPrintCharges.EOF =
False … ?</span></font></p>
<p class=MsoNormal><font size=2 color=blue face=Tahoma><span style='font-size:
10.0pt;font-family:Tahoma;color:blue'> </span></font></p>
<p class=MsoNormal><font size=2 color=blue face=Tahoma><span style='font-size:
10.0pt;font-family:Tahoma;color:blue'>Thanks,</span></font></p>
<p class=MsoNormal><font size=2 color=blue face=Tahoma><span style='font-size:
10.0pt;font-family:Tahoma;color:blue'>Mark</span></font></p>
<p class=MsoNormal><font size=2 color=blue face=Tahoma><span style='font-size:
10.0pt;font-family:Tahoma;color:blue'> </span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>-----Original Message-----<br>
<b><span style='font-weight:bold'>From:</span></b> Chris McIsaac
[mailto:cjm@haleyaldrich.com] <br>
<b><span style='font-weight:bold'>Sent:</span></b> Wednesday, February 12, 2003
9:38 AM<br>
<b><span style='font-weight:bold'>To:</span></b>
dba-sqlserver@databaseadvisors.com<br>
<b><span style='font-weight:bold'>Subject:</span></b> RE: [dba-SQLServer]Do
Loop in a sproc</span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=blue face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:blue'>The format is...</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=blue face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:blue'>WHILE (condition = true)</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=blue face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:blue'>BEGIN</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font><font size=2
color=blue face=Arial><span style='font-size:10.0pt;font-family:Arial;
color:blue'>-- do processing</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=blue face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:blue'>END</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=blue face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:blue'>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).</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
</div>
<blockquote style='margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt'>
<p class=MsoNormal style='margin-right:0in;margin-bottom:12.0pt;margin-left:
.5in'><font size=2 face=Tahoma><span style='font-size:10.0pt;font-family:Tahoma'>-----Original
Message-----<br>
<b><span style='font-weight:bold'>From:</span></b> Mark Boyd
[mailto:MarkBoyd@mcbeeassociates.com] <br>
<b><span style='font-weight:bold'>Sent:</span></b> 12 February 2003 9:26 AM<br>
<b><span style='font-weight:bold'>To:</span></b> SQLServerList<br>
<b><span style='font-weight:bold'>Subject:</span></b> [dba-SQLServer]Do Loop in
a sproc</span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>I am converting VBA code to a
stored procedure.</span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>This code includes a pretty large
Do Loop.</span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>Is it possible to create a loop in
a sproc?</span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>If so, what is the format?</span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>Thanks,</span></font></p>
<p class=MsoAutoSig style='margin-left:.5in'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>Mark Boyd</span></font></p>
<p class=MsoAutoSig style='margin-left:.5in'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>Sr. Systems Analyst</span></font></p>
<p class=MsoAutoSig style='margin-left:.5in'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>McBee Associates, Inc.</span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
</blockquote>
</div>
</body>
</html>