RETURN statement in SQL procedures

The RETURN statement is used to unconditionally and immediately terminate an SQL procedure by returning the flow of control to the caller of the stored procedure.

It is mandatory that when the RETURN statement is executed that it return an integer value. If the return value is not provided, the default is 0. The value is typically used to indicate success or failure of the procedure's execution. The value can be a literal, variable, or an expression that evaluates to an integer value.

You can use one or more RETURN statements in a stored procedure. The RETURN statement can be used anywhere after the declaration blocks within the SQL-procedure-body.

To return multiple output values, parameters can be used instead. Parameter values must be set prior to the RETURN statement being executed.

  CREATE PROCEDURE return_test (IN p_empno CHAR(6),
                                IN p_emplastname VARCHAR(15) )
  LANGUAGE SQL
  SPECIFIC return_test
  BEGIN

    DECLARE v_lastname VARCHAR (15);

    SELECT lastname INTO v_lastname
      FROM employee
  	 WHERE empno = p_empno;

    IF v_lastname = p_emplastname THEN
      RETURN 1;
    ELSE
      RETURN -1;
    END IF;

  END rt

In the example, if the parameter p_emplastname matches the value stored in table employee, the procedure returns 1. If it does not match, it returns -1.