CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2cursor
Kiki
Kiki

Reputation: 2253

SQL cursor result in infinite loop

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.

Upvotes: 0

Views: 1193

Answers (1)

Andrey Korneyev
Andrey Korneyev

Reputation: 26846

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.

Upvotes: 4

Related Questions