A cursor with the name......already exists.

[nnmmss]

i have this stored procedure

CREATE PROCEDURE CheckPRF_PV_Date @projId2 int,@TotalDelivered int,@Result varchar(100) output
AS

declare PRF_Cursor Cursor
For SELECT Quantity,DeadLine FROM dbo.VGoods WHERE (dbo.VGoods.PrjId = @projId2)

open PRF_Cursor
Fetch PRF_Cursor into @RecQuantity,@RecPRFDate
declare PV_Cursor Cursor
For SELECT Delivered,SendDate FROM dbo.SPVDate WHERE (dbo.SPVDate .PrjId = @projId2)
open PV_Cursor
Fetch PV_Cursor into @RecDelivered,@RecPVDate
while (@TempDelivered>0)
begin

if @RecPVDate> @RecPRFDate
begin
set @Result='True'
Return
end
if @RecQuantity>@RecDelivered
begin
set @TempDelivered=@TempDelivered - @RecDelivered
Fetch PV_Cursor into @RecDelivered,@RecPVDate
end
else
begin
set @TempDelivered=@TempDelivered - @RecQuantity
Fetch PRF_Cursor into @RecQuantity,@RecPRFDate
end



end
close PRF_Cursor
deallocate PRF_Cursor
close PV_Cursor
deallocate PV_Cursor
GO


which i call it from another stored procedure
exec CheckPRF_PV_Date @projId,@Total_Delivered,@GoodsId,@TypeId,@TechId, @alarm output

all variables are declared in program , i just have delete it to make the code clear.

the problem is that when i execute the stored procedure from query analyzer i get this error message

A cursor with the name 'PRF_Cursor' already exists.
The cursor is already open.
A cursor with the name 'PV_Cursor' already exists.
The cursor is already open.

this happens for fecch section in

if @RecQuantity>@RecDelivered
begin
set @TempDelivered=@TempDelivered - @RecDelivered
Fetch PV_Cursor into @RecDelivered,@RecPVDate
end
else
begin
set @TempDelivered=@TempDelivered - @RecQuantity
Fetch PRF_Cursor into @RecQuantity,@RecPRFDate
end


if i use FETCH NEXT FROM PV_Cursor or FETCH NEXT FROM PRF_Cursor no error happens but if i want to save the value in the value like Fetch PRF_Cursor into @RecQuantity,@RecPRFDate the error appear

is there any body to help me?

 

[vongrunt]

This code is asymmetrical. When this happens:

     if @RecPVDate> @RecPRFDate
     begin
          set @Result='True'
          [!]Return[/!]
     end

... cursors are left open on server. Next time...

Instead of RETURN, try BREAK statement - it will exit WHILE loop and code flow will continue with CLOSE/DEALLOCATE stuff.

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]