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