SQL WHILE loop provides us with the advantage to execute the SQL statement(s) repeatedly until the specified condition result turn out to be false.
In the following sections of this article, we will use more flowcharts in order to explain the notions and examples. For this reason, firstly, we will explain what is a flowchart briefly. The flowchart is a visual geometric symbol that helps to explain algorithms visually. The flowchart is used to simply design and document the algorithms. In the flowchart, each geometric symbol specifies different meanings.
The following flowchart explains the essential structure of the WHILE loop in SQL:
As you can see, in each iteration of the loop, the defined condition is checked, and then, according to the result of the condition, the code flow is determined. If the result of the condition is true, the SQL statement will be executed. Otherwise, the code flow will exit the loop. If any SQL statement exists outside the loop, it will be executed.
SQL WHILE loop syntax and example
The syntax of the WHILE loop in SQL looks like as follows:
|
1 2 3 4 |
WHILE condition BEGIN {...statements...} END |
After these explanations, we will give a very simple example of a WHILE loop in SQL. In the example given below, the WHILE loop example will write a value of the variable ten times, and then the loop will be completed:
|
1 2 3 4 5 6 7 |
DECLARE @Counter INT SET @Counter=1 WHILE ( @Counter <= 10) BEGIN PRINT 'The counter value is = ' + CONVERT(VARCHAR,@Counter) SET @Counter = @Counter + 1 END |
Now, we will handle the WHILE loop example line by line and examine it with details.
In this part of the code, we declare a variable, and we assign an initializing value to it:
|
1 2 |
DECLARE @Counter INT SET @Counter=1 |
This part of the code has a specified condition that until the variable value reaches till 10, the loop continues and executes the PRINT statement. Otherwise, the while condition will not occur, and the loop will end:
|
1 |
WHILE ( @Counter <= 10) |
In this last part of the code, we executed the SQL statement, and then we incremented the value of the variable:
|
1 2 3 4 |
BEGIN PRINT 'The counter value is = ' + CONVERT(VARCHAR,@Counter) SET @Counter = @Counter + 1 END |
The following flowchart illustrates this WHILE loop example visually:
Infinite SQL WHILE loop
In the infinite loop AKA endless loop, the condition result will never be false, so the loop never ends and can work forever. Imagine that we have a WHILE loop, and we don’t increment the value of the variable. In this scenario, the loop runs endlessly and never ends. Now, we will realize this scenario with the help of the following example. We need to take account of one thing that we should not forget to cancel the execution of the query manually:
|
1 2 3 4 5 6 7 |
DECLARE @Counter INT SET @Counter=1 WHILE ( @Counter <= 10) BEGIN PRINT 'Somebody stops me!' END |
In the following flowchart, it is obvious that the value of the variable never changes; therefore, the loop never ends. The reason for this issue is that the variable is always equal to 1 so the condition returns true for each iteration of the loop:
BREAK statement
BREAK statement is used in the SQL WHILE loop in order to exit the current iteration of the loop immediately when certain conditions occur. In the generally IF…ELSE statement is used to check whether the condition has occurred or not. Refer to the SQL IF Statement introduction and overview article for more details about the IF…ELSE statement.
The following example shows the usage of the BREAK statement in the WHILE loop:
|
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE @Counter INT SET @Counter=1 WHILE ( @Counter <= 10) BEGIN PRINT 'The counter value is = ' + CONVERT(VARCHAR,@Counter) IF @Counter >=7 BEGIN BREAK END SET @Counter = @Counter + 1 END |
In this example, we have checked the value of the variable, and when the value is equal or greater than 7, the code entered the IF…ELSE block and executed the BREAK statement, and so it exited the loop immediately. For this reason, the message shows the values of the variable up to 7. If the condition of the IF…ELSE statement does not meet, the loop will run until the condition result will be false. The following flowchart explains the working logic of the BREAK statement example as visually:
CONTINUE statement
CONTINUE statement is used in the SQL WHILE loop in order to stop the current iteration of the loop when certain conditions occur, and then it starts a new iteration from the beginning of the loop. Assume that we want to write only even numbers in a WHILE loop. In order to overcome this issue, we can use the CONTINUE statement. In the following example, we will check whether the variable value is odd or even. If the variable value is odd, the code enters the IF…ELSE statement blocks and increment the value of the variable, execute the CONTINUE statement and starts a new iteration:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
DECLARE @Counter INT SET @Counter=1 WHILE ( @Counter <= 20) BEGIN IF @Counter % 2 =1 BEGIN SET @Counter = @Counter + 1 CONTINUE END PRINT 'The counter value is = ' + CONVERT(VARCHAR,@Counter) SET @Counter = @Counter + 1 END |
The following flowchart explains the working logic of the CONTINUE statement example as visually:
Reading table records through the WHILE loop
In the following example, we will read table data, row by row. Firstly we will create a sample table:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
USE tempdb GO DROP TABLE IF EXISTS SampleTable CREATE TABLE SampleTable (Id INT, CountryName NVARCHAR(100), ReadStatus TINYINT) GO INSERT INTO SampleTable ( Id, CountryName, ReadStatus) Values (1, 'Germany', 0), (2, 'France', 0), (3, 'Italy', 0), (4, 'Netherlands', 0) , (5, 'Poland', 0) SELECT * FROM SampleTable |
In this step, we will read all data row by row with the help of the WHILE loop:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
USE tempdb GO DECLARE @Counter INT , @MaxId INT, @CountryName NVARCHAR(100) SELECT @Counter = min(Id) , @MaxId = max(Id) FROM SampleTable WHILE(@Counter IS NOT NULL AND @Counter <= @MaxId) BEGIN SELECT @CountryName = CountryName FROM SampleTable WHERE Id = @Counter PRINT CONVERT(VARCHAR,@Counter) + '. country name is ' + @CountryName SET @Counter = @Counter + 1 END |
In this example, we read the table rows via the WHILE loop. We can also develop more sophisticated and advanced loops based on our needs.
Conclusion
In this article, we learned the SQL WHILE loop with quite simple examples. We also virtualized and explained the examples with flowcharts. WHILE loop helps us to achieve iterative operations in SQL Server. At the same time, BREAK and CONTINUE statements can be used to control iteration of the WHILE loop in SQL Server.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023