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

jwcolby jwcolby at colbyconsulting.com
Thu Sep 23 10:07:27 CDT 2010


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.
>



More information about the dba-SQLServer mailing list