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

First, the condition inside the SQL Server While loop tested. If the condition is True, the SQL 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 the while loop.

SQL While Loop Flow Chart

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

SQL WHILE LOOP FLOW CHART

The While loop in SQL Server will check the condition at the beginning of the loop.

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

Let us see the While loop example for better understanding

SQL While Loop Example

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

-- SQL While Loop Example
--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 Server 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 loop. 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 while loop. This statement will execute when the while condition is either True or False

PRINT @Total;

Infinite While Loop in SQL Server

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

-- Infinite SQL While Loop Example
--Declaring Number and Total Variables
DECLARE @Number INT = 1 ;

WHILE @Number < =   10
BEGIN
   PRINT @Number;
   -- SET @Number = @Number + 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 @Number = @Number + 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 While Loop article to understand the functionality of nested loops.