CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008
chrysst
chrysst

Reputation: 357

SQL Server : stuck in WHILE loop using @@fetch_status

I have tried to update the mtrl table using while loop with @@fetch_status but it looks that something is happen and there is an infinite loop.

When I run the following code in SQL Server, it gets stuck.

Any idea what is going wrong?

USE [TEST_DB]
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

BEGIN
    declare @iteid int;

    SET NOCOUNT ON;
    Declare items cursor for
        select mtrl 
        from mtrl 
        where sodtype = 51 and company = 1 and socurrency = 1;

    open items;

    fetch next from items into @iteid;

    while @@fetch_status = 0
    begin
        update mtrl
        set pricer = 2
        where mtrl = @iteid;
    end

    close items;
    deallocate items;
END
GO

Upvotes: 0

Views: 1712

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

You forgot to add another FETCH statement inside your WHILE loop:

open items;
fetch next from items into @iteid;

while @@fetch_status=0
begin
    update mtrl
    set pricer=2
    where mtrl = @iteid;

    fetch next from items into @iteid;
end

However, seeing your query, you should not be using a CURSOR for this simple task:

update mtrl
    set pricer = 2
where
    sodtype = 51
    and company = 1
    and socurrency = 1;

Upvotes: 5

Related Questions