<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Message</TITLE>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.2600.0" name=GENERATOR>
<STYLE>@font-face {
font-family: Tahoma;
}
@page Section1 {size: 8.5in 11.0in; margin: 1.0in 1.25in 1.0in 1.25in; }
P.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
LI.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
DIV.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
A:link {
COLOR: blue; TEXT-DECORATION: underline
}
SPAN.MsoHyperlink {
COLOR: blue; TEXT-DECORATION: underline
}
A:visited {
COLOR: purple; TEXT-DECORATION: underline
}
SPAN.MsoHyperlinkFollowed {
COLOR: purple; TEXT-DECORATION: underline
}
P.MsoAutoSig {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
LI.MsoAutoSig {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
DIV.MsoAutoSig {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
SPAN.emailstyle17 {
FONT-WEIGHT: normal; COLOR: windowtext; FONT-STYLE: normal; FONT-FAMILY: Tahoma; TEXT-DECORATION: none
}
SPAN.EmailStyle19 {
FONT-WEIGHT: normal; COLOR: blue; FONT-STYLE: normal; FONT-FAMILY: Tahoma; TEXT-DECORATION: none
}
DIV.Section1 {
page: Section1
}
</STYLE>
</HEAD>
<BODY lang=EN-US vLink=purple link=blue>
<DIV><FONT face=Arial color=#0000ff size=2>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...</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2> UPDATE tblPrintCharges set
ExceptionCode = ExceptionCode + "18," where EndServDate = '' <BR> UPDATE
tblPrintCharges set ExceptionCode = ExceptionCode + "19," where CPT =
''<BR> etc.</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT><FONT face=Arial color=#0000ff
size=2></FONT><FONT face=Arial color=#0000ff size=2></FONT><BR><FONT face=Arial
color=#0000ff size=2> -- trim the final comma<BR> UPDATE
tblPrintCharges set ExceptionCode = left(ExceptionCode,len(ExceptionCode) - 1)
WHERE ExceptionCode IS NOT NULL AND ExceptionCode <> ''<BR></FONT></DIV>
<DIV><SPAN class=421492515-12022003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT
face=Tahoma size=2>-----Original Message-----<BR><B>From:</B> Mark Boyd
[mailto:MarkBoyd@mcbeeassociates.com] <BR><B>Sent:</B> 12 February 2003 10:18
AM<BR><B>To:</B> dba-sqlserver@databaseadvisors.com<BR><B>Subject:</B> RE:
[dba-SQLServer]Do Loop in a sproc<BR><BR></FONT></DIV>
<DIV class=Section1>
<P class=MsoNormal><FONT face=Tahoma color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Tahoma">Chris
–</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Tahoma">The code is as
follows.</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Tahoma">It was written by
another programmer about 7 years ago.</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Tahoma">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 face=Tahoma color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Tahoma"></SPAN></FONT> </P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: Tahoma">Set db =
CurrentDb()</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: Tahoma">Set rstPrint =
db.OpenRecordset("SELECT * FROM tblPrintCharges")</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: Tahoma"> If
rstPrint.EOF = False Then</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: Tahoma">
rstPrint.MoveFirst</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: Tahoma">
Do Until rstPrint.EOF = True</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: Tahoma">
Me.txtStatus = "Processing record '" & rstPrint!PatientNo &
"'"</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: Tahoma">
Me.Repaint</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: Tahoma">
If Nz(rstPrint!EndServDate) = "" Then</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: Tahoma">
rstPrint.Edit</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: Tahoma">
rstPrint!ExceptionCode = rstPrint!ExceptionCode & "18,"</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: Tahoma">
rstPrint.Update</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: Tahoma">
End If</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: Tahoma">
If Nz(rstPrint!CPT) = "" Then</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: Tahoma">
rstPrint.Edit</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: Tahoma">
rstPrint!ExceptionCode = rstPrint!ExceptionCode & "19,"</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: Tahoma">
rstPrint.Update</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: Tahoma">
End If</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: Tahoma">
If Nz(rstPrint!BegServDate) = "" Then</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: Tahoma">
rstPrint.Edit</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: Tahoma">
rstPrint!ExceptionCode = rstPrint!ExceptionCode & "20,"</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: Tahoma">
rstPrint.Update</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: Tahoma">
End If</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: Tahoma">
If Nz(rstPrint!Charge) = "" Then</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: Tahoma">
rstPrint.Edit</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: Tahoma">
rstPrint!ExceptionCode = rstPrint!ExceptionCode & "23,"</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: Tahoma">
rstPrint.Update</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: Tahoma">
End If</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: Tahoma">
rstPrint.MoveNext</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: Tahoma">
</SPAN></FONT><FONT face=Tahoma color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: Tahoma">Loop</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: Tahoma">
Else</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: Tahoma">
MsgBox "There are no outstanding charges.", vbCritical, "No
Charges"</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: Tahoma">
End If</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Tahoma"></SPAN></FONT> </P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Tahoma">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 face=Tahoma color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Tahoma">Can I say WHILE
tblPrintCharges.EOF = False … ?</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Tahoma"></SPAN></FONT> </P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Tahoma">Thanks,</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Tahoma">Mark</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Tahoma"></SPAN></FONT> </P>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; 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: 0.5in"><FONT face=Arial color=#0000ff
size=2><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Arial color=blue
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">The
format is...</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Arial color=#0000ff
size=2><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Arial color=blue
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">WHILE
(condition = true)</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Arial color=blue
size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">BEGIN</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"> </SPAN></FONT><FONT
face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">-- do
processing</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">
</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Arial color=blue
size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">END</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Arial color=#0000ff
size=2><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Arial color=blue
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">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: 0.5in"><FONT face=Arial color=#0000ff
size=2><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P></DIV>
<BLOCKQUOTE style="MARGIN-TOP: 5pt; MARGIN-BOTTOM: 5pt; MARGIN-RIGHT: 0in">
<P class=MsoNormal
style="MARGIN-BOTTOM: 12pt; MARGIN-LEFT: 0.5in; MARGIN-RIGHT: 0in"><FONT
face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; 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: 0.5in"><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">I am converting VBA code to a
stored procedure.</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">This code includes a pretty
large Do Loop.</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">Is it possible to create a loop
in a sproc?</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">If so, what is the
format?</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">Thanks,</SPAN></FONT></P>
<P class=MsoAutoSig style="MARGIN-LEFT: 0.5in"><FONT face=Tahoma
size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">Mark
Boyd</SPAN></FONT></P>
<P class=MsoAutoSig style="MARGIN-LEFT: 0.5in"><FONT face=Tahoma
size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">Sr. Systems
Analyst</SPAN></FONT></P>
<P class=MsoAutoSig style="MARGIN-LEFT: 0.5in"><FONT face=Tahoma
size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">McBee Associates,
Inc.</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN
style="FONT-SIZE: 12pt"></SPAN></FONT> </P></BLOCKQUOTE></DIV></BLOCKQUOTE></BODY></HTML>