<!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.2719.2200" name=GENERATOR>
<STYLE>@font-face {
font-family: Wingdings;
}
@font-face {
font-family: Tahoma;
}
@page Section1 {size: 612.0pt 792.0pt; margin: 72.0pt 90.0pt 72.0pt 90.0pt; }
P.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman"
}
LI.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman"
}
DIV.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman"
}
H2 {
FONT-SIZE: 14pt; MARGIN: 12pt 0cm 3pt; FONT-STYLE: italic; FONT-FAMILY: Arial
}
H3 {
FONT-SIZE: 13pt; MARGIN: 12pt 0cm 3pt; FONT-FAMILY: Arial
}
H4 {
FONT-SIZE: 12pt; MARGIN: 3pt 0cm; FONT-FAMILY: Arial
}
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: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman"
}
LI.MsoAutoSig {
FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman"
}
DIV.MsoAutoSig {
FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman"
}
P.Code {
FONT-SIZE: 12pt; MARGIN: 6pt 0cm 6pt 17.85pt; FONT-FAMILY: "Courier New"; punctuation-wrap: simple
}
LI.Code {
FONT-SIZE: 12pt; MARGIN: 6pt 0cm 6pt 17.85pt; FONT-FAMILY: "Courier New"; punctuation-wrap: simple
}
DIV.Code {
FONT-SIZE: 12pt; MARGIN: 6pt 0cm 6pt 17.85pt; FONT-FAMILY: "Courier New"; punctuation-wrap: simple
}
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
}
SPAN.EmailStyle21 {
COLOR: navy; FONT-FAMILY: Arial
}
SPAN.EmailStyle22 {
COLOR: #993366; FONT-FAMILY: Arial
}
SPAN.EmailStyle23 {
COLOR: navy; FONT-FAMILY: Arial
}
DIV.Section1 {
page: Section1
}
</STYLE>
</HEAD>
<BODY lang=EN-US vLink=purple link=blue>
<DIV><SPAN class=250092723-12022003><FONT face=Arial color=#0000ff size=2>Hi
Arthur:</FONT></SPAN></DIV>
<DIV><SPAN class=250092723-12022003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=250092723-12022003><FONT face=Arial color=#0000ff size=2>I have
found that single line statements are not as fast. In situations
with queries that call other queries when I have spent time to merge the
two queries, the results have always been slower(?) Maybe this is a MBD
thing... Of course in SQL7/2000 you can be as sloppy as you like. An aside:
Coldfusion has the capability to hold the results from a previous query, to
MySQL and use those results in a subsequent query...very
fast.</FONT></SPAN></DIV>
<DIV><SPAN class=250092723-12022003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=250092723-12022003><FONT face=Arial color=#0000ff
size=2>Comments</FONT></SPAN></DIV>
<DIV><SPAN class=250092723-12022003><FONT face=Arial color=#0000ff
size=2>Jim</FONT></SPAN></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B>
dba-sqlserver-admin@databaseadvisors.com
[mailto:dba-sqlserver-admin@databaseadvisors.com]<B>On Behalf Of </B>Arthur
Fuller<BR><B>Sent:</B> Wednesday, February 12, 2003 11:22 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=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">Don’t you love these
one-statement solutions? I certainly do! AFAIC, the less logic in the FE the
better. This is one of the big wars I wage in the MySQL community, which seems
to prefer just the opposite </SPAN></FONT><FONT face=Wingdings color=navy
size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Wingdings">J</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"></SPAN></FONT> </P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">All I want to do from
Access is execute sprocs and udfs &c. Let the logic reside in the sprocs,
and ban table access. That’s my approach in a nutshell. Then it doesn’t matter
what FE they use, could be Access, QA, </SPAN></FONT><FONT face=Arial
color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">Delphi</SPAN></FONT><FONT
face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">, who cares. If you
don’t have the keys you don’t get in.</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"></SPAN></FONT> </P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">A.</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"></SPAN></FONT> </P>
<P class=MsoNormal style="MARGIN-LEFT: 36pt"><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>
dba-sqlserver-admin@databaseadvisors.com
[mailto:dba-sqlserver-admin@databaseadvisors.com] <B><SPAN
style="FONT-WEIGHT: bold">On Behalf Of </SPAN></B>Djabarov, Robert<BR><B><SPAN
style="FONT-WEIGHT: bold">Sent:</SPAN></B> February 12, 2003 11:55
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: 36pt"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P>
<P class=MsoNormal style="MARGIN-LEFT: 36pt"><SPAN class=EmailStyle22><FONT
face=Arial color=#993366 size=2><SPAN style="FONT-SIZE: 10pt">Ooops… it should
be as this:</SPAN></FONT></SPAN></P>
<P class=MsoNormal style="MARGIN-LEFT: 36pt"><SPAN class=EmailStyle22><FONT
face=Arial color=#993366 size=2><SPAN
style="FONT-SIZE: 10pt"></SPAN></FONT></SPAN> </P>
<P class=MsoNormal style="MARGIN-LEFT: 36pt"><FONT face=Arial color=blue
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">UPDATE
tblPrintCharges set ExceptionCode = left(ExceptionCode,len(ExceptionCode) - 1)
WHERE substring(ltrim(reverse(isnull(ExceptionCode, ‘ ‘))), 1, 1) =
‘,’</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 72pt"><FONT face=Tahoma color=black
size=3><SPAN
style="FONT-SIZE: 12pt; COLOR: black; FONT-FAMILY: Tahoma"></SPAN></FONT> </P>
<P class=MsoNormal style="MARGIN-LEFT: 72pt"><FONT face=Tahoma color=black
size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Tahoma">-----Original
Message-----<BR><B><SPAN style="FONT-WEIGHT: bold">From:</SPAN></B> Djabarov,
Robert <BR><B><SPAN style="FONT-WEIGHT: bold">Sent:</SPAN></B> Wednesday,
February 12, 2003 10:51 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: 72pt"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P>
<P class=MsoNormal style="MARGIN-LEFT: 72pt"><FONT face=Arial color=blue
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">UPDATE
tblPrintCharges set ExceptionCode = left(ExceptionCode,len(ExceptionCode) - 1)
WHERE charindex(‘,’, ltrim(reverse(ExceptionCode))) > 0</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 72pt"><SPAN class=EmailStyle21><FONT
face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt"></SPAN></FONT></SPAN> </P>
<P class=MsoNormal style="MARGIN-LEFT: 72pt"><SPAN class=EmailStyle21><FONT
face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt"></SPAN></FONT></SPAN> </P>
<P class=MsoNormal style="MARGIN-LEFT: 108pt"><FONT face=Tahoma color=black
size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: black; 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: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: 108pt"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P>
<P class=MsoNormal style="MARGIN-LEFT: 108pt"><FONT face=Arial color=blue
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">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>
<P class=MsoNormal style="MARGIN-LEFT: 108pt"><FONT face="Times New Roman"
color=black size=3><SPAN
style="FONT-SIZE: 12pt; COLOR: black"></SPAN></FONT> </P>
<P class=MsoNormal style="MARGIN-LEFT: 108pt"><FONT face=Arial color=blue
size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial"> UPDATE
tblPrintCharges set ExceptionCode = ExceptionCode + "18," where EndServDate =
'' <BR> UPDATE tblPrintCharges set ExceptionCode = ExceptionCode + "19,"
where CPT = ''<BR> etc.</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 108pt"><FONT face="Times New Roman"
color=black size=3><SPAN
style="FONT-SIZE: 12pt; COLOR: black"></SPAN></FONT> </P>
<P class=MsoNormal style="MARGIN-LEFT: 108pt"><FONT face="Times New Roman"
color=black size=3><SPAN
style="FONT-SIZE: 12pt; COLOR: black"><BR></SPAN></FONT><FONT face=Arial
color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial"> -- trim the
final comma<BR> UPDATE tblPrintCharges set ExceptionCode =
left(ExceptionCode,len(ExceptionCode) - 1) WHERE ExceptionCode IS NOT NULL AND
ExceptionCode <> ''</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 108pt"><FONT face="Times New Roman"
color=black size=3><SPAN
style="FONT-SIZE: 12pt; COLOR: black"></SPAN></FONT> </P>
<P class=MsoNormal style="MARGIN-BOTTOM: 12pt; MARGIN-LEFT: 144pt"><FONT
face=Tahoma color=black size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: black; 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: 144pt"><FONT face=Tahoma color=blue
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Tahoma">Chris
–</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 144pt"><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 style="MARGIN-LEFT: 144pt"><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 style="MARGIN-LEFT: 144pt"><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 style="MARGIN-LEFT: 144pt"><FONT face="Times New Roman"
color=black size=3><SPAN
style="FONT-SIZE: 12pt; COLOR: black"></SPAN></FONT> </P>
<P class=MsoNormal style="MARGIN-LEFT: 144pt"><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 style="MARGIN-LEFT: 144pt"><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 style="MARGIN-LEFT: 144pt"><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 style="MARGIN-LEFT: 144pt"><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 style="MARGIN-LEFT: 144pt"><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 style="MARGIN-LEFT: 144pt"><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 style="MARGIN-LEFT: 144pt"><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 style="MARGIN-LEFT: 144pt"><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 style="MARGIN-LEFT: 144pt"><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 style="MARGIN-LEFT: 144pt"><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 style="MARGIN-LEFT: 144pt"><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 style="MARGIN-LEFT: 144pt"><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 style="MARGIN-LEFT: 144pt"><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 style="MARGIN-LEFT: 144pt"><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 style="MARGIN-LEFT: 144pt"><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 style="MARGIN-LEFT: 144pt"><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 style="MARGIN-LEFT: 144pt"><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 style="MARGIN-LEFT: 144pt"><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 style="MARGIN-LEFT: 144pt"><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 style="MARGIN-LEFT: 144pt"><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 style="MARGIN-LEFT: 144pt"><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 style="MARGIN-LEFT: 144pt"><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 style="MARGIN-LEFT: 144pt"><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 style="MARGIN-LEFT: 144pt"><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 style="MARGIN-LEFT: 144pt"><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 style="MARGIN-LEFT: 144pt"><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 style="MARGIN-LEFT: 144pt"><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 style="MARGIN-LEFT: 144pt"><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 style="MARGIN-LEFT: 144pt"><FONT face=Tahoma color=blue
size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: Tahoma">
Loop</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 144pt"><FONT face=Tahoma color=blue
size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: Tahoma">
Else</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 144pt"><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 style="MARGIN-LEFT: 144pt"><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 style="MARGIN-LEFT: 144pt"><FONT face="Times New Roman"
color=black size=3><SPAN
style="FONT-SIZE: 12pt; COLOR: black"></SPAN></FONT> </P>
<P class=MsoNormal style="MARGIN-LEFT: 144pt"><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 style="MARGIN-LEFT: 144pt"><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 style="MARGIN-LEFT: 144pt"><FONT face="Times New Roman"
color=black size=3><SPAN
style="FONT-SIZE: 12pt; COLOR: black"></SPAN></FONT> </P>
<P class=MsoNormal style="MARGIN-LEFT: 144pt"><FONT face=Tahoma color=blue
size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Tahoma">Thanks,</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 144pt"><FONT face=Tahoma color=blue
size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Tahoma">Mark</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 144pt"><FONT face="Times New Roman"
color=black size=3><SPAN
style="FONT-SIZE: 12pt; COLOR: black"></SPAN></FONT> </P>
<P class=MsoNormal style="MARGIN-LEFT: 180pt"><FONT face=Tahoma color=black
size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: black; 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: 180pt"><FONT face="Times New Roman"
color=black size=3><SPAN
style="FONT-SIZE: 12pt; COLOR: black"></SPAN></FONT> </P>
<P class=MsoNormal style="MARGIN-LEFT: 180pt"><FONT face=Arial color=blue
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">The
format is...</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 180pt"><FONT face="Times New Roman"
color=black size=3><SPAN
style="FONT-SIZE: 12pt; COLOR: black"></SPAN></FONT> </P>
<P class=MsoNormal style="MARGIN-LEFT: 180pt"><FONT face=Arial color=blue
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">WHILE
(condition = true)</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 180pt"><FONT face=Arial color=blue
size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">BEGIN</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 180pt"><FONT face="Times New Roman"
color=black size=3><SPAN
style="FONT-SIZE: 12pt; COLOR: black"> </SPAN></FONT><FONT
face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">-- do
processing</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 180pt"><FONT face="Times New Roman"
color=black size=3><SPAN
style="FONT-SIZE: 12pt; COLOR: black"> </SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 180pt"><FONT face=Arial color=blue
size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">END</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 180pt"><FONT face="Times New Roman"
color=black size=3><SPAN
style="FONT-SIZE: 12pt; COLOR: black"></SPAN></FONT> </P>
<P class=MsoNormal style="MARGIN-LEFT: 180pt"><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>
<P class=MsoNormal style="MARGIN-LEFT: 180pt"><FONT face="Times New Roman"
color=black size=3><SPAN
style="FONT-SIZE: 12pt; COLOR: black"></SPAN></FONT> </P>
<P class=MsoNormal
style="MARGIN-BOTTOM: 12pt; MARGIN-LEFT: 216pt; MARGIN-RIGHT: 0cm"><FONT
face=Tahoma color=black size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: black; 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: 216pt"><FONT face=Tahoma color=black
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Tahoma">I am
converting VBA code to a stored procedure.</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 216pt"><FONT face=Tahoma color=black
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Tahoma">This
code includes a pretty large Do Loop.</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 216pt"><FONT face=Tahoma color=black
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Tahoma">Is it
possible to create a loop in a sproc?</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 216pt"><FONT face=Tahoma color=black
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Tahoma">If so,
what is the format?</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 216pt"><FONT face="Times New Roman"
color=black size=3><SPAN
style="FONT-SIZE: 12pt; COLOR: black"></SPAN></FONT> </P>
<P class=MsoNormal style="MARGIN-LEFT: 216pt"><FONT face=Tahoma color=black
size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Tahoma">Thanks,</SPAN></FONT></P>
<P class=MsoAutoSig style="MARGIN-LEFT: 216pt"><FONT face=Tahoma color=black
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Tahoma">Mark
Boyd</SPAN></FONT></P>
<P class=MsoAutoSig style="MARGIN-LEFT: 216pt"><FONT face=Tahoma color=black
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Tahoma">Sr.
Systems Analyst</SPAN></FONT></P>
<P class=MsoAutoSig style="MARGIN-LEFT: 216pt"><FONT face=Tahoma color=black
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Tahoma">McBee
Associates, Inc.</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 216pt"><FONT face="Times New Roman"
color=black size=3><SPAN
style="FONT-SIZE: 12pt; COLOR: black"></SPAN></FONT> </P></DIV></BLOCKQUOTE></BODY></HTML>