The 365 Data Science team is proud to invite you to our own community forum. A very well built system to support your queries, questions and give the chance to show your knowledge and help others in their path of becoming Data Science specialists.
Ask
Anybody can ask a question
Answer
Anybody can answer
Vote
The best answers are voted up and moderated by our team

Stored procedures IN and OUT

Stored procedures IN and OUT

Super Learner
0
Votes
1
Answer

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?

1 Answer

365 Team
0
Votes

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

×
EXTENDED SALE
Learn Data Science this Summer!
Get 50% OFF