Returning Single Value from Stored Procedure

Currently MySQL does not allow you to return a value from a procedure using the RETURN statement, but there are several workarounds for this problem :

CREATE FUNCTION sp_existing_employee
 (v_employee_name VARCHAR(128), 
  v_employee_id INTEGER) 
RETURNS INT 
BEGIN 
 IF(EXISTS(SELECT * FROM employees 
            WHERE employee_name = v_employee_name AND 
             employee_id = v_employee_id)) then 
   RETURN 0; 
  END IF; 

RETURN 1; 
END; 

and then call the procedure as

SET status = sp_returnvalue(...) 
or 
SELECT sp_returnvalue(...) INTO status 

CREATE PROCEDURE sp_existing_employee
 (v_employee_name VARCHAR(128), 
  v_employee_id INTEGER,
  INOUT status INT) 
L_return: 
BEGIN 
  IF(EXISTS(SELECT * FROM employees 
             WHERE employee_name = v_employee_name AND 
                   employee_id = v_employee_id)) then  
    SET status = 0; 
    LEAVE L_return; 
  END IF; 

SET status = 1; 
END; 

CREATE FUNCTION sp_existing_employee
 (v_employee_name VARCHAR(128), 
  v_employee_id INTEGER) 
RETURNS INT 
BEGIN 
 IF(EXISTS(SELECT * FROM employees 
            WHERE employee_name = v_employee_name AND 
             employee_id = v_employee_id)) then 
   SELECT 0;
   RETURN; 
  END IF; 

SELECT 1; 
RETURN; 
END; 

Equivalents in Other Databases

If you have any other questions, please contact us: support@ispirer.com