Cursors

[TomBarrand]

I want the following SQL statements to be executed then update FOB.Purch_Ord_Line_No with the Purch_Ord_Line.Purch_Ord_Line_No.

I thought about using two cursors so that I could make sure of the FETCH NEXT. Below is an example of what data might be returned from the SELECT statements. I would then want to update 1 with 5, 2 with 6, 7 with 8, etc. Any suggestions?

Purch_Ord_Line FOB
5 1
6 2
7 3
8 4

SELECT Purch_Ord_Line_No
FROM Purch_Ord_Line
WHERE Purch_Ord_No = 1887 AND Revision_No = 1

SELECT dbo.FOB.Purch_Ord_Line_No
FROM dbo.FOB INNER JOIN
dbo.Purch_Ord_Line ON dbo.FOB.Purch_Ord_Line_No = dbo.Purch_Ord_Line.Purch_Ord_Line_No AND dbo.Purch_Ord_Line.Purch_Ord_No = 1887 AND
dbo.Purch_Ord_Line.Revision_No = 1



DECLARE PurchOrdLine_POL_Curs CURSOR
FOR

SELECT Purch_Ord_Line_No
FROM Purch_Ord_Line
WHERE Purch_Ord_No = 1887 AND Revision_No = 1
FOR READ ONLY
GO

DECLARE @POLNo Int
OPEN PurchOrdLine_POL_Curs

DECLARE FOB_POL_Curs CURSOR
FOR

SELECT dbo.FOB.Purch_Ord_Line_No
FROM dbo.FOB INNER JOIN
dbo.Purch_Ord_Line ON dbo.FOB.Purch_Ord_Line_No = dbo.Purch_Ord_Line.Purch_Ord_Line_No AND dbo.Purch_Ord_Line.Purch_Ord_No = 1887 AND
dbo.Purch_Ord_Line.Revision_No = 1
FOR UPDATE
GO

DECLARE @FOBPOLNo Int
OPEN FOB_POL_Curs

FETCH NEXT FROM PurchOrdLine_POL_Curs INTO @POLNo
WHILE @@Fetch_Status = 0 BEGIN

FETCH NEXT FROM FOB_POL_Curs INTO @FOBPOLNo
WHILE @@Fetch_Status = 0 BEGIN
UPDATE FOB
SET Purch_Ord_Line_No = @POLNo
WHERE CURRENT OF FOB_POL_Curs

FETCH NEXT FROM PurchOrdLine_POL_Curs INTO @POLNo
FETCH NEXT FROM FOB_POL_Curs INTO @FOBPOLNo

END

CLOSE PurchOrdLine_POL_Curs

DEALLOCATE PurchOrdLine_POL_Curs

CLOSE FOB_POL_Curs

DEALLOCATE FOB_POL_Curs