SQL cursor result in infinite loop

Question

I'm trying to insert invoice items to existing invoice with negative value for quantity. I decided to use cursor for this but when I run the query it results in infinite loop.

Here is my code:

declare @cKey char(13);
set @cKey = '1512000000043';

-- declare cursor to get
-- all items for specific invoice
declare c cursor
for
    select
        acIdent, anQty
    from
        tHE_MoveItem where acKey = @cKey;

declare @cIdent  char (16),
        @nQty decimal(19,6),
        @nNo int,
        @cStatus varchar(2),   
        @cErrorOut varchar(1024);
open c
fetch c into @cIdent, @nQty
while (@@fetch_status=0)
begin

    -- add all items with negative qty
    -- to same invoice
    select @cIdent;
    -- invert value
    set @nQty = @nQty *-1;
    select @nQty;

    -- insert ident with negative value to invoice
    EXEC dbo.pHE_MoveItemsCreAll @cKey, @cIdent,@nQty, '', 1, @nNo OUTPUT,@cErrorOut OUTPUT,@cStatus OUTPUT;

    fetch c into @cIdent, @nQty
end

close c
deallocate c

I'm using SQL Server 2008 R2.

The procedure pHE_MoveItemsCreAll is inserting values in same table as the cursor reads from.

It is not quite clear from your code - are you inserting records into the same table you're reading from? — Andrey Korneyev
– Andrey Korneyev, Commented Apr 28, 2015 at 6:58
How do you conclude that your code block went to infinite loop.? May be Procedure execution take more time also. Comment that proc execution and try — knkarthick24
– knkarthick24, Commented Apr 28, 2015 at 7:06
@knkarthick24 becouse invoice only had one item on it and after i force closed the execution it had 500+ rows :) — Kiki
– Kiki, Commented Apr 28, 2015 at 7:07
Answer is already added by @AndyKorneyev. modify your code accordingly. — knkarthick24
– knkarthick24, Commented Apr 28, 2015 at 7:10

Andrey Korneyev · Accepted Answer · 2015-04-28 07:00:58Z

4

You have to declare your cursor using static keyword (declare c cursor static) to prevent fetching newly inserted records back to cursor.

A static cursor always displays the result set as it was when the cursor was opened. In other case when you're inserting your records into the same table and they met conditions of data selected into cursor - these new records will be retrieved and cursor iterates again.

answered Apr 28, 2015 at 7:00

Andrey Korneyev

26.9k16 gold badges73 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kiki Over a year ago

Thank you! I totaly forgot about static. Now it works like a charm :)

Collectives™ on Stack Overflow

SQL cursor result in infinite loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related