[dba-SQLServer]Do Loop in a sproc

MessageHi Arthur:

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.

Comments
Jim
  -----Original Message-----
  From: dba-sqlserver-admin at databaseadvisors.com
[mailto:dba-sqlserver-admin at databaseadvisors.com]On Behalf Of Arthur Fuller
  Sent: Wednesday, February 12, 2003 11:22 AM
  To: dba-sqlserver at databaseadvisors.com
  Subject: RE: [dba-SQLServer]Do Loop in a sproc


  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 J



  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,
Delphi, who cares. If you don’t have the keys you don’t get in.



  A.



  -----Original Message-----
  From: dba-sqlserver-admin at databaseadvisors.com
[mailto:dba-sqlserver-admin at databaseadvisors.com] On Behalf Of Djabarov,
Robert
  Sent: February 12, 2003 11:55 AM
  To: dba-sqlserver at databaseadvisors.com
  Subject: RE: [dba-SQLServer]Do Loop in a sproc



  Ooops… it should be as this:



  UPDATE tblPrintCharges set ExceptionCode =
left(ExceptionCode,len(ExceptionCode) - 1) WHERE
substring(ltrim(reverse(isnull(ExceptionCode, ‘ ‘))), 1, 1) = ‘,’



  -----Original Message-----
  From: Djabarov, Robert
  Sent: Wednesday, February 12, 2003 10:51 AM
  To: dba-sqlserver at databaseadvisors.com
  Subject: RE: [dba-SQLServer]Do Loop in a sproc



  UPDATE tblPrintCharges set ExceptionCode =
left(ExceptionCode,len(ExceptionCode) - 1) WHERE charindex(‘,’,
ltrim(reverse(ExceptionCode))) > 0





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