How to LOOP in MySQL Stored Procedure

A loop construct in SQL and major programming languages refers to a construct that repeatedly executes instructions provided that the specified condition is true. The set of instructions executed inside a loop can be a single query or a collection of commands leading to a larger query set.

In this tutorial, we will discuss how we can implement a MySQL loop within a procedure.

For this tutorial, we assume you are aware of how to work with the MySQL procedures.

Features of MySQL Loop

Before we dive into the “how” of implementing a MySQL loop, let’s list several loop features:

1. A loop can contain more than one statement where each statement is terminated with a semicolon.
2. All the statements and values inside the loop are continually executed, but only if the set condition is true.
3. You can terminate a loop by using the LEAVE statement.
4. MySQL allows you to give a name to the loop using the syntax loopName: LOOP

Basic Usage

The general syntax for implementing a simple MySQL loop is:

The statement list should also include a LEAVE condition that specifies when the loop should terminate.

The label represents the name of the loop.

The syntax below shows the loop implementation with a LEAVE statement:

Example Use Case

In this section, I will try to illustrate how to use a LOOP in a procedure. The purpose of the procedure is to implement a simple loop and will not reflect real-world data.

Consider the procedure below that implements a loop to find even numbers:

Once we call the loop, we get the values of even numbers separated by spaces as specified in the CONCAT() function.

<img data-lazy-src="https://kirelos.com/wp-content/uploads/2021/05/echo/word-image-487.png" height="59" src="data:image/svg xml,” width=”143″>

Conclusion

In this tutorial, we covered the basics of using a MySQL loop to iterate through a set of instructions. MySQL loops perform similarly to loops in other programming languages such as Python, C , and JavaScript.