MySQL LEAVE Statement – Terminate Stored Procedures, Functions and Loops

By /
Mysql Leave Statement

Did you ever try to exit the loop or block of code in MySQL and struggle to achieve so? If yes then this tutorial will help you in an easy way. In this tutorial, we will learn how to exit from the block of code or break the block of code in MySQL with the help of the LEAVE statement. This is a very helpful element in MySQL that very few people know about. So, we will study it with syntax and an example.

Introduction to MySQL LEAVE Statement

By definition, the MySQL LEAVE statement is used to break the flow control of the block of code that has the given label. It is mainly used for stored programs and if the label is specified for the outermost block, it exits the program.

The LEAVE statement is used within the BEGIN…END block or loops such as WHILE, LOOP and REPEAT. It is exactly the same as the break statement available in many programming languages such as Java, C and C++.

Syntax to use MySQL LEAVE Statement

The LEAVE statement can be used inside the loops or begin-end blocks with the help of labels. The following block of code shows how can we use the LEAVE statement in MySQL.

Syntax 1- LEAVE Statement with BEGIN-END Block

CREATE PROCEDURE proc()
block_label: BEGIN
    IF condition THEN
        LEAVE block_label;
    END IF;
     other_statements
END$$
Code language: SQL (Structured Query Language) (sql)

Where,

  • proc() – It is a procedure created using the CREATE PROCEDURE statement.
  • block_label – It is a name that is given to the block. You can give labels to loops as well as begin-end blocks.
  • LEAVE block_label – It terminates the block when a certain condition becomes true and passes the flow control outside of the block

Syntax 2- LEAVE Statement with Loops

As mentioned earlier, we can use the LEAVE statement with MySQL loops as well such as LOOP, REPEAT and WHILE.

[label]: LOOP/REPEAT/WHILE
    IF condition THEN
        LEAVE [label];
    END IF;
    -- statements
[UNTIL search_condition (only for REPEAT loop)] 
END LOOP/REPEAT/WHILE [label];Code language: SQL (Structured Query Language) (sql)

“UNTIL search_condition” is used only for the REPEAT loop.

In the above syntax, you can see that the label is optional. Whereas, when you write the BEGIN-END block, you must specify the label.

Examples of MySQL LEAVE Statement

Now we will see an example to understand the LEAVE statement effectively.

We will create a stored procedure of the name “leaveExample” and write a LOOP loop. We will accept two values from the user as parameters to the procedure.

If the first value is smaller than the second value then we will increment the first value till the second value and print all of them. On the other hand, If the first value is larger than the second value then we will decrement the first value till the second value and print them.

DELIMITER //
CREATE PROCEDURE leaveExample(IN x INT,IN y INT)
BEGIN
  DECLARE outp VARCHAR(100) DEFAULT ' ';
  
  IF(x>y) THEN
    decr:LOOP
     SET outp = CONCAT(outp,x,',');
       IF(x=y) THEN
       LEAVE decr;
       END IF;
     SET x= x-1;
    END LOOP;
  
  ELSEIF(x<y) THEN
    incr:LOOP
      SET outp = CONCAT(outp,x,',');
      IF(x=y) THEN
        LEAVE incr;
      END IF;
      SET x=x+1;
    END LOOP;
      
  Else
     SET outp="Both are Equal, can't iterate";
   
 END IF;
 SELECT outp as output;
END //Code language: SQL (Structured Query Language) (sql)
Create Stored Procedure
Create Stored Procedure

Explanation-

  • In the above example, we have used the IF-THEN-ELSE statement to check whether the first number is greater or second.
  • If the first number is greater, we will start the loop of label “decr”. We use the CONCAT() function to concatenate all numbers into a single string.
  • Then we again use the IF condition to check if both numbers become similar.
  • We then decrement the value of x until it becomes the same as y. If the x becomes the same as y, we will break the loop using the LEAVE statement. This way, the control goes out of the loop and then the outer IF-THEN-ELSE block ends.
  • Similarly, if the first number is smaller, we will start the loop of label “incr” to increment the value of x and perform the similar operation as mentioned above.

Now let’s test the procedure to check if it works correctly.

CALL leaveExample(1,10);Code language: SQL (Structured Query Language) (sql)
Output
Output

Here you can see, we have received the output of numbers 1 to 10.

Let’s pass the same parameters in reverse order.

CALL leaveExample(10,1);Code language: SQL (Structured Query Language) (sql)
Output
Output

As you can see, we got numbers 1 to 10 in reverse order.

Now let’s pass the two same parameters and see what we get.

CALL leaveExample(10,10);Code language: SQL (Structured Query Language) (sql)
Output
Output

Here, we are getting a message that both numbers are equal and the loop can’t be iterated exactly as we programmed.

Summary

In this tutorial, we learned –

  • What the LEAVE statement is and what it is used for.
  • Syntax of the LEAVE statement.
  • Example of the LEAVE statement using stored procedure and the MySQL LOOP loop.

If you like this tutorial, don’t forget to share it with your friends and stay connected with mysqlcode.com for such interesting tutorials.