The SQL While Loop is used to repeat a block of statements for given number of times, until the given condition is False. SQL Server While loop start with the condition and, if the condition is True then statements inside the BEGIN..END block will be executed otherwise, it won’t be executed.
It means, while loop in SQL server may execute zero or more time and the syntax of while loop is:
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 compiler will check the condition inside the Sql Server While loop. If the Condition is True then the statement or group of statements under the inside the BEGIN..END block will be executed. If the Condition is False then compiler will come out of the loop 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
The While loop in SQL Server will check the condition at the beginning of the loop.
- If the condition is True then it will execute the statements inside the loop.
- Next we have to use Arithmetic Operators inside the while loop to increment and decrements the value.
- After the value is incremented, again it will check for the condition. As long as the condition is True, the statements inside the while loop will be executed.
- If the condition is False then it will exit from the While loop
Let us see the While loop example for better understanding
SQL While Loop Example
This while loop example allows the user to enter an integer value below 10. Using this value, compiler will add those values to sum up 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
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 and this statement will be executed when the while condition is either True or False
Infinite While Loop in SQL Server
If you forgot to increment or decrement the value inside the while loop then SQL while loop will execute infinite times (also called as 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
Here Number is always 1 and it is always less than 10 so PRINT statement inside the while loop will go on execute infinite 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
TIP: Please refer Nested While Loop article to understand the functionality of nested loops.