Resolved: Calling parametric procedure with Out parameter
Following along with the video, I came up with this code:
USE employees;
DROP PROCEDURE IF EXISTS emp_avg_salary_out; DELIMITER $$
USE employees $$
CREATE PROCEDURE emp_avg_salary_out (in p_emp_no integer, out p_avg_salary decimal(10,2))
BEGIN
SELECT
ROUND(AVG(s.salary),2) AS avg_salary
INTO p_avg_salary FROM
employees e
JOIN
salaries s ON e.emp_no = s.emp_no
WHERE
e.emp_no = p_emp_no;
END $$
DELIMITER ; CALL emp_avg_salary_out(11300); My call statement returned an error. I found that it should be written as CALL emp_avg_salary_out(11300, @p_avg_salary); but this is not covered in the video because the procedure was called from the schemas section. Can you provide more detail? Thank you, Mike
DROP PROCEDURE IF EXISTS emp_avg_salary_out; DELIMITER $$
USE employees $$
CREATE PROCEDURE emp_avg_salary_out (in p_emp_no integer, out p_avg_salary decimal(10,2))
BEGIN
SELECT
ROUND(AVG(s.salary),2) AS avg_salary
INTO p_avg_salary FROM
employees e
JOIN
salaries s ON e.emp_no = s.emp_no
WHERE
e.emp_no = p_emp_no;
END $$
DELIMITER ; CALL emp_avg_salary_out(11300); My call statement returned an error. I found that it should be written as CALL emp_avg_salary_out(11300, @p_avg_salary); but this is not covered in the video because the procedure was called from the schemas section. Can you provide more detail? Thank you, Mike
1 answers ( 0 marked as helpful)
Hi Mike!
Thanks for reaching out.
Since by construction you have an out parameter as well, you need to designate where/how(i.e. in which variable) do you want to store the output of the procedure while calling it. That's why the code that you have found works.
Hope this helps.
Best,
Martin
Best,
Martin