SQL Server Loop

DECLARE @MinCost DECIMAL(10, 2);
DECLARE @MaxCost DECIMAL(10, 2);
DECLARE @PercentageIncrease DECIMAL(5, 2);
DECLARE @RowCount INT;
DECLARE @Counter INT = 1;

-- Create a temporary table
CREATE TABLE #CostIncrease (
    MinCost DECIMAL(10, 2),
    MaxCost DECIMAL(10, 2),
    PercentageIncrease DECIMAL(5, 2)
);

-- Insert 5 rows into the temporary table
INSERT INTO #CostIncrease (MinCost, MaxCost, PercentageIncrease)
VALUES
(0, 50, 15.00),
(51, 100, 12.50),
(101, 150, 10.00),
(151, 200, 7.50),
(201, 250, 5.00);

-- Select the data to verify the insertion
SELECT * FROM #CostIncrease;

-- Get the total number of rows in the temporary table
SELECT @RowCount = COUNT(*) FROM #CostIncrease;

WHILE @Counter <= @RowCount
BEGIN
    -- Fetch the current row's values
    SELECT
        @MinCost = MinCost,
        @MaxCost = MaxCost,
        @PercentageIncrease = PercentageIncrease
    FROM #CostIncrease
    WHERE ROW_NUMBER() OVER (ORDER BY MinCost) = @Counter;

    -- Update the cost of products within the current cost range
    UPDATE Products
    SET Cost = Cost + (Cost * @PercentageIncrease / 100)
    WHERE Cost >= @MinCost AND Cost <= @MaxCost;

    -- Move to the next row
    SET @Counter = @Counter + 1;
END;

-- Select the updated Products table
SELECT * FROM Products;

-- Drop the temporary table when done
DROP TABLE #CostIncrease;
Copy