<!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;}
span.EmailStyle20
{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'>I agree with your recommendation about
avoiding a loop.</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 think creating a sproc with multiple
UPDATE queries is definitely the way to go.</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 for the insight,</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
10:41 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'>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, <br>
but the performance is horrible. A better way...</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'> UPDATE
tblPrintCharges set ExceptionCode = ExceptionCode + "18," where
EndServDate = '' <br>
UPDATE tblPrintCharges set ExceptionCode = ExceptionCode +
"19," where CPT = ''<br>
etc.</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=3 face="Times New Roman"><span
style='font-size:12.0pt'><br>
</span></font><font size=2 color=blue face=Arial><span style='font-size:10.0pt;
font-family:Arial;color:blue'> -- trim the final comma<br>
UPDATE tblPrintCharges set ExceptionCode =
left(ExceptionCode,len(ExceptionCode) - 1) WHERE ExceptionCode IS NOT NULL AND
ExceptionCode <> ''</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 10:18 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=2 color=blue
face=Tahoma><span style='font-size:10.0pt;font-family:Tahoma;color:blue'>Chris
–</span></font></p>
<p class=MsoNormal style='margin-left:.5in'><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 style='margin-left:.5in'><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 style='margin-left:.5in'><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 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=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 style='margin-left:.5in'><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 style='margin-left:.5in'><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 style='margin-left:.5in'><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 style='margin-left:.5in'><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 style='margin-left:.5in'><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 style='margin-left:.5in'><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 style='margin-left:.5in'><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 style='margin-left:.5in'><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 style='margin-left:.5in'><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 style='margin-left:.5in'><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 style='margin-left:.5in'><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 style='margin-left:.5in'><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 style='margin-left:.5in'><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 style='margin-left:.5in'><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 style='margin-left:.5in'><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 style='margin-left:.5in'><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 style='margin-left:.5in'><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 style='margin-left:.5in'><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 style='margin-left:.5in'><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 style='margin-left:.5in'><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 style='margin-left:.5in'><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 style='margin-left:.5in'><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 style='margin-left:.5in'><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 style='margin-left:.5in'><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 style='margin-left:.5in'><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 style='margin-left:.5in'><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 style='margin-left:.5in'><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 style='margin-left:.5in'><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 style='margin-left:.5in'><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 style='margin-left:.5in'><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 style='margin-left:.5in'><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 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 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 style='margin-left:.5in'><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 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 color=blue
face=Tahoma><span style='font-size:10.0pt;font-family:Tahoma;color:blue'>Thanks,</span></font></p>
<p class=MsoNormal style='margin-left:.5in'><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 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:1.0in'><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:1.0in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
<div>
<p class=MsoNormal style='margin-left:1.0in'><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:1.0in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:1.0in'><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:1.0in'><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:1.0in'><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:1.0in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:1.0in'><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:1.0in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:1.0in'><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:1.0in'><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:
1.0in'><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:1.0in'><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:1.0in'><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:1.0in'><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:1.0in'><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:1.0in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
<p class=MsoNormal style='margin-left:1.0in'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>Thanks,</span></font></p>
<p class=MsoAutoSig style='margin-left:1.0in'><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:1.0in'><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:1.0in'><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:1.0in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
</blockquote>
</blockquote>
</div>
</body>
</html>