I have a SP that contains a cursor with an inner cursor. The SP works mostly but in the inner cursor I get an error after it updates the first record. Below is the info:
Error:
It actually does update the first record of the inner cursor; seems like after the update the cursor is no more.
Thanks for your thoughs.
Error:
Code:
[COLOR=red]
"Error: Number (560) Severity (16) State (1) Procedure (_test)
The UPDATE/DELETE WHERE CURRENT OF failed for the cursor 'my_curse_bos'
because it is not positioned on a row."
[/color]
Code:
[COLOR=blue]
DECLARE my_curse_def CURSOR FOR (SELECT part, qty FROM #availdef )
DECLARE @reccount INT
OPEN my_curse_def
FETCH my_curse_def into @part, @def_qty
WHILE (@@sqlstatus = 0)
BEGIN
SELECT @varqty=@def_qty
SELECT @reccount=0
SELECT @reccount = (SELECT COUNT(*) FROM #bosiwant WHERE part=@part)
DECLARE my_curse_bos CURSOR FOR (SELECT part, qty_ord, allocate, asl_ord, t_num, line FROM #bosiwant WHERE part=@part) FOR UPDATE OF allocate
OPEN my_curse_bos
FETCH my_curse_bos into @curPart, @qty_ord, @allocate, @asl_ord, @t_num, @line
WHILE (@reccount>0)
BEGIN
IF @varqty>0
BEGIN
IF @qty_ord>=@varqty
BEGIN
update #bosiwant
set allocate = @varqty
[COLOR=red]where current of my_curse_bos[/color]
SELECT @varqty=0
END
ELSE
BEGIN
update #bosiwant
set allocate = @qty_ord
[COLOR=red]where current of my_curse_bos[/color]
SELECT @varqty=@varqty-@qty_ord
END
END
FETCH my_curse_bos into @curPart, @qty_ord, @allocate, @asl_ord, @t_num, @line
SELECT @reccount=@reccount-1
END
CLOSE my_curse_bos
deallocate cursor my_curse_bos
FETCH my_curse_def into @part, @def_qty
END
close my_curse_def
deallocate cursor my_curse_def
[/color]
It actually does update the first record of the inner cursor; seems like after the update the cursor is no more.
Thanks for your thoughs.