Stored procedures IN and OUT

Question

Hi, I have a question regarding the exercise about stored procedures.
When I was verifying the codes, I choose the employee Georgi Facelli as input but MySQL gave me an error "Result consisted of more than one row".
I checked the database and we do indeed have two employees with that name.
So is there a way that we can show more row with stored procedures?

Answer 1

Hi Lisa! Thanks for reaching out. Please accept my apologies for the delayed response. In fact, we have only provided this procedure as an exercise and it has been meant to work for records that contain a unique combination of first and last name of the individual. To solve this problem, a GROUP BY p_first_name clause can be added to the SELECT statement within the procedure. Here's the entire code which you can use as a reference.

DROP PROCEDURE IF EXISTS emp_info;

DELIMITER $$

CREATE PROCEDURE emp_info(in p_first_name varchar(255), in p_last_name varchar(255), out p_emp_no integer)

BEGIN
SELECT
e.emp_no
INTO p_emp_no FROM
employees e
WHERE
e.first_name = p_first_name
AND e.last_name = p_last_name
GROUP BY p_first_name;

END$$

DELIMITER ;

CALL emp_info('Georgi', 'Facello', @p_emp_no);
SELECT @p_emp_no;

Hope this helps.
Best,
Martin

Stored procedures IN and OUT

Submit an answer