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
The While loop in SQL Server will check the condition at the beginning of the loop.
- If the condition is True, then it executes the code within the BEGIN..END statements.
- Within the While loop, we must use SQL Arithmetic Operators to increment and decrements the loop value.
- After the value increase, again SQL Server checks the condition. As long as the condition is True, it continues the process.
- 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
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
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
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.