I am doing a course on SQL on MYSQL workbench. In the course I had an exercise in which I had to take an IN parameter value and save as an OUT parameter value for further use.
I came up with this query:-
DROP PROCEDURE IF EXISTS avg_employee_sal_out;
DELIMITER $$
CREATE PROCEDURE avg_employee_sal_out( IN p_emp_no INT, OUT p_avg_sal DECIMAL(10, 2))
BEGIN
SELECT
AVG(s.salary) INTO p_avg_sal
FROM
employees e
JOIN
salaries s
ON e.emp_no = s.emp_no
WHERE
e.emp_no = p_emp_no;
END $$
DELIMITER ;
Which was the correct answer but when I run the code, the OUT parameter value ‘p_avg_sal’ was asking for an input.
[Screenshot of when I run the procedure from user interface] 1If I run the stored procedure in the interface it was fine if I left the out value blank but when I call the procedure I is showing an error.
[Screenshot when I run the above procedure by command] 2[here is the error screenshot] 3Leaving the OUT parameter null didn’t work, it shows another error
In the course when the lecturer ran the same query by user interface it didn’t ask for the input for OUT value.
(Sorry, can’t provide screenshot cause the lecture is a video and its screenshot is completely black but it the same as [1] if you remove the lower input slot.)
2
Answers
OK got it done... At least half of it.
It was asking for input for OUT parameter because of a bug in the workbench. It got half fixed when I changed "IN" and "OUT" to the original "in" and "out".
DELIMITER $$ CREATE PROCEDURE avg_employee_sal_out( in p_emp_no INT, out p_avg_sal DECIMAL(10, 2)) BEGIN
END $$ DELIMITER ;
By doing this its working correctly by running the procedure on the user interface but to call it you have to use variables.
After changing "IN" and "OUT" to "in" and "out".
To call it by commands someone told me to do with variables like this:- here are the commands.
The bug was still there in workbench 4 years ago.
The number of arguments to the procedure needs to match, but those arguments don’t necessarily need to have a value: