Esat Erkec
Flow chart of the SQL WHILE loop

SQL WHILE loop with simple examples

October 25, 2019 by

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:

Flow chart of the SQL WHILE loop

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:

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:

Result of the WHILE loop example in SQL Server

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:

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:

In this last part of the code, we executed the SQL statement, and then we incremented the value of the variable:

The following flowchart illustrates this WHILE loop example visually:

SQL WHILE loop example flowchart

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:

Infinite SQL WHILE loop  demonstration

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:

Infinite SQL WHILE loop  flowchart

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:

WHILE loop demonstration with BREAK statement

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:

Flow chart of the SQL WHILE loop with BREAK statement

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:

WHILE loop demonstration with CONTINUE statement

The following flowchart explains the working logic of the CONTINUE statement example as visually:

Flow chart of the SQL While loop with CONTINUE statement

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:

SampleTable image

In this step, we will read all data row by row with the help of the WHILE loop:

Reading  table records with SQL WHILE loop

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.

Esat Erkec

Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert.

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
Esat Erkec

Latest posts by Esat Erkec (see all)

396 Views