SQL While Loop

The SQL Server While Loop is used to repeat a block of statements for a given number of times until the given condition is False. The While loop starts with the condition, and if the condition result is True, then statements inside the BEGIN..END block will execute. Otherwise, it won’t execute. It suggests that the while loop may execute zero or more times.

Syntax

The Syntax of a While Loop in SQL Server is as follows:

While Expression
  BEGIN
       statement 1
        statement 2
    ………….
  END
-- This is the statement Outside the block

First, the condition inside the While loop was tested. If the condition is True, the statement or query inside the BEGIN..END block will execute. If the condition is False, it will skip the BEGIN..END block and execute other statements outside of it.

SQL Server While Loop Flow Chart

The following flow chart will explain to you While loop Visually and perfectly.

The While loop will check the condition at the beginning of it.

1. If the condition is True, then it executes the code within the BEGIN..END statements.
2. Within the While loop, we must use the Arithmetic Operators to increment and decrements the value.
3. After the value increase, again, the Server checks the condition. As long as the condition is True, it continues the process.
4. If the expression is evaluated to False exits from the BEGIN..END block.

Let us see the example for a better understanding

SQL Server While Loop Example

In this example, we declared two integer values. Next, we used the While loop to add numbers from 1 to 10.

--Declaring Number and Total Variables
DECLARE @Number INT = 1 ;
DECLARE @Total INT = 0 ;

WHILE @Number < =   10
BEGIN
   SET @Total = @Total + @Number;
   SET @Number = @Number + 1 ;
END
PRINT @Total;

Total = 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 = 55

In this query, First, we created two variables called Number and Total and initialized them to 1, 0 using the following statement

DECLARE @Number INT = 1 ;
DECLARE @Total INT = 0 ;

In the next line, we used the while condition. If the condition result is true, then the number is added to the total. Otherwise, it will exit from the iteration. We also SET the Number value to increment (@Number = @Number + 1). After increment, the process will be repeated until the condition results False (i.e., @Number = 11).

WHILE @Number < =   10
BEGIN
   SET @Total = @Total + @Number;
   SET @Number = @Number + 1 ;
END

In the next line, we used a print statement outside the while loop. This statement will execute when the condition is either True or False.

PRINT @Total;

Infinite While Loop

If you forget to increment (SET @Num = @Num + 1) or decrement the value, the SQL while loop will execute countless times, also called an infinite loop. For example:

-- Infinite Example
--Declaring Num and Total Variables
DECLARE @Num INT = 1 ;

WHILE @Num < =   10
BEGIN
   PRINT @Num;
   -- SET @Num = @Num + 1 ;
END

Output

Messages
-------
1
1
1
1
1
1
1
.
.
.

Here Number is constantly 1, and it is always less than ten. So PRINT statement inside the while loop will go on execute countless times. Now, let us remove the comment from the following statement.

-- SET @Num = @Num + 1 ;

Now, when it reaches 10, the condition will fail. Let us see the output

1
2
3
4
5
6
7
8
9
10

TIP: Please refer to the Nested article to understand the functionality of nested ones.