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

Calling parametric procedure with Out parameter

Calling parametric procedure with Out parameter

Super Learner
0
Votes
1
Answer

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

1 Answer

365 Team

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

Thank you. I suppose I dont understand the syntax of emp_avg_salary_out(11300, @p_avg_salary), particularly the @p_avg_salary. Does the @ make it a variable? I don't recall seeing this before.

3 months

You are very welcome! Yes, you are very right – @ makes it a variable. It is normal to not have seen this earlier – we are talking about variables later in the course, starting here:https://365datascience.teachable.com/courses/360102/lectures/5529194 Hope this helps. Best, Martin

3 months

Ahh! Thank you!

3 months

You are very welcome!
Best,
Martin

3 months