I executed the solution to the exercise but gives me a zero output.
Please help check what is wrong here, the output is zero, not giving the emp_ID as expected for the solution.
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;
END$$
DELIMITER ;
Hi Nike!
Thanks for reaching out.
This code gives us error message 1172 stating that there are multiple individuals that share this first and last names while the procedure has been created for individuals that are unique in terms of first-and-last name combination.
In other words, the query works only for unique combinations of first and last name. In case there is more than 1 person bearing the same first and last name (as is the case with Georgi Facello), the query should provide error code 1172: Result consisted of more than one row; which is something a stored procedure can't handle.
Please, use different employee, which is unique in the table. For example:
set @p_emp_no = 0;call employees.emp_info('Margareta', 'Markovitch', @p_emp_no);select @p_emp_no;
Hope this helps.
Best,
Tsvetelin