[dba-SQLServer] Is a cursor the way to go

Paul Hartland paul.hartland at googlemail.com
Fri May 25 07:35:21 CDT 2012


To all,

I will try and explain the problem as best as I possibly can, for this
purpose lets say I have two tables, PODetail which holds detail records for
a purchase order, and a table containing a value which we have currently
allocated to a single purchase order number POAllocation

PODetail
PoNumber    Amount     Allocated    RowID
123456          50.00             0              1
123456          20.00             0              2
123456          76.00             0              3

POAllocation
PONumber    Allocated
123456          141.00

What I need to do is update the Allocated field in PODetail with a value,
the way I want to do this is get the PO number and allocated amount from
POAllocation and then loop through the PODetail records updating the
Allocated field, so I would come to record (rowid) 1 if the allocated
amount is less than 50.00 then update the allocated field with that value,
if the allocated amount is great than 50.00 then update the allocated field
to 50.00, take 50.00 away from 141.00 to get 91.00 then report the process
for record (rowid) 2.

I am thinking/in middle of writing a cursor inside a cursor, anyone else
any better ideas on how to do this.

Thanks in advance for any help.


-- 
Paul Hartland
paul.hartland at googlemail.com


More information about the dba-SQLServer mailing list