[dba-SQLServer] SQL Server 2005 - Cursors Or Not Cursors

Francisco Tapia fhtapia at gmail.com
Thu Sep 23 10:15:43 CDT 2010


i disagree...
  you should use cursors if there is no other viable way to solve your
problem.  In your scenario, you can certainly build up a subquery to join to
based on date / time stamp that would yield the previous job's location's
postcode.  you will need to somehow know which job was 1st 2nd etc. in order
for your join to work in either scenario (subquery or cursor).




-Francisco
http://bit.ly/sqlthis   | Tsql and More...




On Thu, Sep 23, 2010 at 8:07 AM, jwcolby <jwcolby at colbyconsulting.com>wrote:

> Paul,
>
> As long as this isn't doing tens of thousands of these at a shot I think
> this is the perfect
> application of a cursor.
>
> Cursors are slow but who cares if it is just a dozen at a time?
>
> John W. Colby
> www.ColbyConsulting.com
>
> On 9/23/2010 10:53 AM, Paul Hartland wrote:
> > To all,
> >
> > We have employees that can do more than one job in a day on our database,
> > when an employee is assigned to a job, the application takes the persons
> > home postcode and the jobs location postcode and stores them in a table,
> we
> > are now creating an estimated mileage.  I can do this based on the
> postcodes
> > supplied but it will be inaccurate as the start postcodes will always be
> the
> > employees home, when the start postcode for the second job should really
> be
> > the postcode of the previous job location.
> >
> > At the moment I have a select query which returns results for an employee
> > where they are doing more than one job in a day, result example below:
> >
> > PersonID     Date                 StartCode            EndCode
> > 111111        29/09/2010        AA1 1AA             BB1 1BB
> > 111111        29/09/2010        AA2 2AA             BB2 2BB
> > 111111        29/09/2010        AA3 3AA             BB3 3BB
> > 111111        29/09/2010        AA4 4AA             BB4 4BB
> >
> > I am thinking of putting these results into a cursor and looping through
> the
> > records updating the startcode with the previous records endcode, so that
> > AA2 2AA will become BB1 1BB, AA3 3AA will become BB2 2BB etc
> >
> > Is this the best way to do this, or could someone point me in a better
> > direction please.
> >
> > Many thanks in advance for all your help.
> >
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>



More information about the dba-SQLServer mailing list