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.