WHILE statement in Transact SQL

WHILE is a flow control structure that allows statements to be executed in a loop as long as the condition it validates is TRUE.

Let’s see how it works in the following examples:

WHILE:

DECLARE @i INT  = 1
DECLARE @max INT = 10

WHILE(@i <= @max)
BEGIN
   SELECT @i
   SET @i = @i +1
END

The following example shows a WHILE loop that validates that as long as @i is less than or equal to @max, the value of @i is queried and its value is incremented by 1 and so on ,until @i gets the value 11 and the WHILE condition is FALSE.

To carry out the next example, first we are going to perform several INSERT INTO on the Orders table, whose scripts we leave below:

INSERT INTO Orders (orderId, product, productDescription, price, orderDate, productQuantity, customerId)
VALUES (9, 'Balay Fridge', 'Appliance', '1099.95', '2022-09-15', 1, NULL)

INSERT INTO Orders (orderId, product, productDescription, price, orderDate, productQuantity, customerId)
VALUES (10, 'Smart TV Samsung', 'TV', '799', '2022-09-15', 1, NULL)

INSERT INTO Orders (orderId, product, productDescription, price, orderDate, productQuantity, customerId)
VALUES (11, 'TEKA Microwave', 'Appliance', '199.95', '2022-09-15', 1, NULL)

Currently our Orders table has the following records:

Now let’s move on to showing the example code:

BEGIN
DECLARE @ordersQuantity INT;
SET @ordersQuantity = (SELECT COUNT (1)
                       FROM Orders);
PRINT  'There are ' +
       CAST (@ordersQuantity AS NVARCHAR (10)) +
       ' records in the orders table';

DECLARE @iterator INT = 1;
DECLARE @discount INT = 100;
DECLARE @minPriceDiscount INT = 500;

   WHILE @iterator <= @ordersQuantity
   BEGIN
       BEGIN TRAN;
       PRINT 'We are in record number: '+
              CAST (@iterator AS NVARCHAR (10))
       DECLARE @validatePriceRecord FLOAT;
       SET @validatePriceRecord = (SELECT price
                                   FROM Orders
                                   WHERE orderId = @iterator
                                   AND customerId  IS NULL);
       IF @validatePriceRecord >= @minPriceDiscount
       BEGIN
           UPDATE Orders
           SET price = price - @discount
           WHERE orderId = @iterator;
           PRINT 'The discount has been applied to the'+
           CAST (@iterator AS NVARCHAR (10)) + ' record'
       END
       SET @iterator = @iterator + 1
       COMMIT TRAN;
   END
END

The following example basically applies a discount of 100 euros to those products in the Orders table with a price greater than or equal to 500 euros that do not yet have an customerId linked. The steps are as follows:

1. We save in a @ordersQuantity variable, the total number of records of the Orders table.

2. We go through all the registers in the WHILE thanks to the value of @iterator that will be increased in each round of the loop.

3. We save in a variable: @priceRecordValidate the price of those products that do not yet have an customerId associated.

4. We check in the IF, if the price of @priceRecordValidate is equal to or greater than 500. If it is TRUE, we do an UPDATE of the price of the record by subtracting the 100 discount.

Once the code has been executed, we can check the price of the following affected records:

CONTINUE and BREAK

The CONTINUE and BREAK instructions allow us to continue and stop executions, respectively, and are often used as a control method for the loop executions of WHILE instructions:

DECLARE @i INT  = 1
DECLARE @max INT = 10

WHILE(@i <= @max)
BEGIN
   SELECT @i
   SET @i = @i +1
       IF @i = 7
           BREAK
       ELSE
           CONTINUE
END

In this example we have updated the code from example 1, adding the CONTINUE and BREAK statements. The loop will continue to execute normally until @i equals 7, where we will BREAK and terminate the execution.

This means that the SELECT will only show up to 6 on the console.