CODEWITHSUNDEEP
sql-servert-sqlcursor
AJIT AGARWAL
AJIT AGARWAL

Reputation: 9

Sql Server Cursor infinite loop

DECLARE @UnitPrice DECIMAL(5, 2);
DECLARE @ProductId INT;
DECLARE UnitPriceUpdateCursor CURSOR
FOR
    SELECT ProductID
    FROM Products;
OPEN UnitPriceUpdateCursor;
FETCH NEXT FROM UnitPriceUpdateCursor INTO @ProductId;
WHILE(@@Fetch_Status = 0)
    BEGIN
        SELECT @UnitPrice = UnitPrice
        FROM Products;
        IF(@UnitPrice BETWEEN 5 AND 10)
            BEGIN
                UPDATE Products
                  SET
                      UnitPrice = 15.00
                WHERE ProductID = @ProductId;
        END;
            ELSE
        IF(@UnitPrice BETWEEN 15 AND 20)
            BEGIN
                UPDATE Products
                  SET
                      UnitPrice = 25
                WHERE ProductID = @ProductId;
        END;
        FETCH NEXT FROM UnitPriceUpdateCursor INTO @ProductId;
    END;
CLOSE UnitPriceUpdateCursor;
DEALLOCATE UnitPriceUpdateCursor;
SET NOCOUNT OFF;

Upvotes: 0

Views: 495

Answers (2)

Sean Lange
Sean Lange

Reputation: 33571

You could replace your entire cursor logic with a single update statement. This should do the same thing.

Update Products 
set UnitPrice = case when UnitPrice = 18.00 then 20 
                    when UnitPrice < 25.00 then 30
                    else UnitPrice
                    end

--EDIT-- The question was changed after I posted my original answer. This should accommodate the new logic you want.

Update Products 
set UnitPrice = case when UnitPrice > 5 and UnitPrice < 10 then 15.00 
                    when UnitPrice > 15 and UnitPrice < 20 then 25
                    else UnitPrice
                    end

Upvotes: 3

Emdad
Emdad

Reputation: 832

You can try the following statement

Declare @UnitPrice decimal
Declare @ProductId int
Declare UnitPriceUpdateCursore  Cursor FOR
    Select UnitPrice From Products 
OPEN  UnitPriceUpdateCursore
Fetch Next From UnitPriceUpdateCursore into @UnitPrice
While (@@Fetch_Status=0)
Begin
    select @UnitPrice=UnitPrice from Products where @ProductId=ProductId

    IF(@UnitPrice =18.00)
    Begin 
        Update Products set UnitPrice=20 where ProductId=@ProductId
    End
    ELSE IF(@UnitPrice <25.00)
    Begin
        Update Products Set UnitPrice=30 Where ProductId=@ProductId
    End
    Fetch Next From UnitPriceUpdateCursore into @UnitPrice
End
Close UnitPriceUpdateCursore
Deallocate UnitPriceUpdateCursore

Upvotes: 0

Related Questions