cursor syntax

[TomBarrand]

I intend to use cursors to solve a problem that I have, but I am unfamiliar with them. What I want to do is as follows:

1. Call a stored procedure from an external app and pass a value into @POLineNo.
2. Delete records from FOB_Size table that meet the criteria below.
3. Select FOB_No and FOB_Quantity from FOB
4. Get the first FOB record and hold the details in @FOBNo and @FOBQTY
5. Select Size_No and Ratio from Size
6. Get the first Size_No and Ratio and hold them in @SizeNo and @Ratio
7. Do the following calculation, @SizeQTY = (@Ratio/100) * @FOBQTY
8. Insert a record into FOB_Size with the values stored in @FOBNo, @SizeNo and @SizeQTY
9. As long as you are not at the end of the SizeNo recordset do points 5 to 8 again for the next Size record
10. When the end of the Size_No recordset is reached for the current FOB_No get the next FOB_No in the FOB recordset and follow steps 5 to 8 again

Can anyone help me with the syntax? What I have got so far is below.

Thanks

CREATE PROCEDURE sp_AddAllSizes
@POLineNo INT

AS
DELETE FOB_Size
FROM FOB_Size, FOB
WHERE FOB.FOB_No = FOB_Size.FOB_No AND FOB.Purch_Ord_Line_No = @POLineNo

DECLARE FOBNo_Curs CURSOR
FOR
SELECT FOB_No, FOB_Quantity
FROM FOB
WHERE Purch_Ord_Line_No = @POLineNo
FOR READ ONLY
GO

DECLARE @FOBNo INT, @FOBQTY INT

FETCH NEXT FROM FOBNo_Curs into @FOBNo, @FOBQTY
WHILE @@Fetch_Status = 0 BEGIN

DECLARE @SizeNo INT
SELECT Size_No, Ratio
FROM SIZE
WHERE Purch_Ord_Line_No = @POLineNo

END
CLOSE FOBNo_Curs
return
GO

 

[Guest_imported]

Tom,

1)The DECLARE @SizeNo INT
should be outside the loop. BTW, you never use @SizeNo .

2)Why use cursors? what you are proposing sounds easier using set operations and it will perform faster that way.

3) (minor point) write your JOIN expression in the from clause instead of the where clause so your first sql statement would be:
DELETE FOB_Size
FROM FOB_Size INNER JOIN FOB ON FOB.FOB_No = FOB_Size.FOB_No
WHERE FOB.Purch_Ord_Line_No = @POLineNo

4)The cursor must be OPENed before FETCHing from it:
OPEN FOBNo_Curs

5)The built-in stroed procs are prefixed with 'sp_'. Keep yours apart by using a different prefix(es).

6) instead of "What I want to DO as follows:", try stating what you want to achieve. That leaves the solution space more open (unless of course you are absolutely sure that teh sequence of steps outlines is the one and only way you are prepared to consider).

7)If I understand you correctly (having the relevant tabel structures listed would help), you have:
FOB (FOBNo, POLineNo) PK:FOBNo
FOB_size (FOB_no, FOB_qty, SizeNo, SizeQty) PK:FOB_no, SizeNo
Size (sizeNo, Ratio, POLIneNo) PK: sizeNo
You then want something like:

insert FOB_size (SizeNo, FOBNo, SizeQty)
select FOB_size.SizeNo, FOB_size.FOBNo, ((Size.Ratio/100) * FOB_size.FOBQty) AS FOB_size.SizeQty
from FOB_size inner join Size on FOB_size.SizeNo = Size.SizeNo
inner join FOB on FOB.FOBNo = FOB_size.FOBNo
where FOB.Purch_Ord_Line_No = @POLineNo

That preceded by the delete you already have should do the trick a lot faster and simpler than the cursor method.

mono