While Loop in T-SQL

-- Step 1: Create Products table
Create Table Products (
    ProductID Int Primary Key,
    ProductName Varchar(500),
    ItemInStock Int
)

-- Step 2: Insert sample product data
Insert Into Products (ProductID, ProductName, ItemInStock)
Values
(1, 'Candy', 50),
(2, 'Drink', 100),
(3, 'Nuggets', 200);

Select * From Products (Nolock)

-- Step 3: Create Sales table
Create Table Sales (
    SaleID Int Primary Key,
    ProductID Int,
    QuantitySold Int
)

-- Step 4: Insert sample sales data
Insert Into Sales (SaleID, ProductID, QuantitySold)
Values
(1, 1, 5),
(2, 2, 10),
(3, 3, 20)

Select * From Sales (Nolock)
-- Step 5: Declare loop variables
Declare 
  @SaleID Int, 
  @ProductID Int, 
  @QuantitySold Int

-- Step 6: WHILE loop to update product stock levels based on sales
Select 
  @SaleID = SaleID,
  @ProductID = ProductID,
  @QuantitySold = QuantitySold
From
(
Select Top 1
  SaleID,
  ProductID,
  QuantitySold  
From
  Sales (Nolock)
Order By
  SaleID
) As Temp
While @@ROWCOUNT = 1
Begin

    -- Update stock level in Products table
    Update Products
    Set ItemInStock = ItemInStock - @QuantitySold
    Where 
  ProductID = @ProductID;

    -- Move to the next sale
  Select 
    @SaleID = SaleID,
    @ProductID = ProductID,
    @QuantitySold = QuantitySold
  From
  (
  Select Top 1
    SaleID,
    ProductID,
    QuantitySold  
  From
    Sales (Nolock)
  Where
    SaleID [ > ] @SaleID
  Order By
    SaleID
  ) As Temp
End

-- Step 6: Check updated product stock levels
Select * From Products (Nolock)