MySQL: RETURN Statement

This tutorial explains how to use the RETURN statement in MySQL with syntax and examples.

Description

In MySQL, the RETURN statement is used when you are want to exit a function and return the result of the function. It can also be used to terminate a LOOP and then exit with the result.

Syntax

The syntax for the RETURN statement in MySQL is:

RETURN result;

Parameters or Arguments

result

The result that is to be returned by the function.

Note

• The RETURN statement can be used in a function to create an exit point. Each function you create in MySQL must have at least one RETURN statement, though it can have more than one RETURN statement if there are multiple exit points in the function.
• The RETURN statement can also be used to terminate a LOOP and then exit with the function result.

Example

Let's look at an example that shows how to use the RETURN statement in MySQL:

DELIMITER //

CREATE FUNCTION CalcIncome ( starting_value INT )
RETURNS INT

BEGIN

   DECLARE income INT;

   SET income = 0;

   label1: LOOP
     SET income = income + starting_value;
     IF income  3000 THEN
       ITERATE label1;
     END IF;
     LEAVE label1;
   END LOOP label1;

   RETURN income;

END; //

DELIMITER ;

In this RETURN example, the function called CalcIncome will exit when it encounters the RETURN statement and return the value stored in the income variable as the result of the function.

You could have also used the RETURN statement to terminate the loop called label1. For example:

DELIMITER //

CREATE FUNCTION CalcIncome ( starting_value INT )
RETURNS INT

BEGIN

   DECLARE income INT;

   SET income = 0;

   label1: LOOP
     SET income = income + starting_value;
     IF income  3000 THEN
       ITERATE label1;
     END IF;
     RETURN income;
   END LOOP label1;

   RETURN starting_value;

END; //

DELIMITER ;

In this RETURN example, we use the RETURN statement twice.

The first RETURN statement is within the LOOP statement and when encountered will exit the LOOP, exit the function, and return the value stored in the variable called income.

The second RETURN statement is used at the end of the function to exit the function and return the value stored in the starting_value variable.

In this example, we have used the RETURN statement to create 2 exit points in the function.