OUT parameter needs INPUT-CodePudding

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] 1

If 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

Leaving the OUT parameter null didn't work, it shows another error

[here is the error screenshot] 3

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.)

CodePudding user response：

The number of arguments to the procedure needs to match, but those arguments don't necessarily need to have a value:

CALL employees.avg_employee_sal_out(11203, NULL)

CodePudding user response：

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

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 ;

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.