How do I code my stored proc with a cursor?

[ChrisH2]

Can someone advise me on the best method to use to code my stored procedure without using a cursor?

I have the following tables: -

Order header, Order lines, receipts and allocations.

One Order header to many order lines
One Order line to many receipts


I want to insert a row in the allocations table per row in the order lines file.
For each order line I also need to sum the receipts file to calculate the quantity expected (total qty received - total qty invoiced). With this total I need to then calculate the expected value, I already have a function for this called POlineValue.
I have included some cut down code that I started, it's fragmented but you should get the idea of what I am trying to achieve.
Is it possible without a cursor or temporary table?



INSERT INTO POPalloc
(
po_ref, line_no, qty_expected, expected_value
)
SELECT
@PORef, ordl.line_no, xxx, POLineValue(xxx, ordl.cost_price, ordl.disc_perc, ordl.uos)
FROM POPordline AS ordl
WHERE ordl.po_ref = @PORef


xxx being: -

SELECT sum(qty_received - qty_invoiced) FROM POPreceipt
WHERE po_ref = @PORef
AND line_no = line number from select on the insert

 

[ChrisH2]

OK, I worked it out.

May be better ways of doing it. I did it using two statements. It was something like this incase anyone wants to know.

INSERT INTO POPalloc
(
po_ref, line_no, qty_expected, expected_value
)
SELECT
@PORef, ordl.line_no,
(SELECT sum(qty_received - qty_invoiced) FROM POPreceipt AS rect
WHERE ordl.po_ref = rect.po_ref
AND ordl.line_no = rect.line_no),
0
FROM POPordline AS ordl
WHERE ordl.po_ref = @PORef

UPDATE POPreceipt SET
expected_value = POLineValue(rect.qty_expected, ordl.cost_price, ordl.disc_perc, ordl.uos),
FROM POPreceipt AS rect
INNER JOIN POPorders1 AS ordl
ON rect.po_ref = ordl.po_ref
AND rect.line_no = ordl.line_no
WHERE rect.po_ref = @PORef

 

[SQLSister]

For the future, do the update first, then the insert. Inthe order you are doing it, the update will also update all the rows you just inserted. It will be less processing to just update the rows which need updating and then insert the new rows.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief