RETURN statement in SQL procedures

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

When the RETURN statement runs, it must 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 before the RETURN statement runs.

  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 p_emplastname parameter matches the value that is stored in the employee table, the procedure returns 1. If the values do not match, the procedure returns -1.