SQL While Loop

The SQL While Loop is used to repeat a block of statements for a given number of times until the given condition is False. SQL Server 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 time

SQL While Loop 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 SQL Server While loop 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 While Loop Flow Chart

The following flow chart will explain you SQL Server While loop Visually and perfectly

WHILE LOOP FLOW CHART

The While loop in SQL Server 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 evaluated to False, exits from the BEGIN..END block.

Let us see the example for better understanding

SQL While Loop Example

In this example, we declared two integer values. Next, we used 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

SQL While Loop 1

In this Sql while loop query, First, we created two variables called Number, 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 while condition. If the condition result is true then 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 print statement outside the SQL while loop. This statement will execute when the the condition is either True or False

PRINT @Total;

Infinite While Loop

If you forgot 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 Nested article to understand the functionality of nested ones.