HOW TO INVOKE A PROCEDURE WITH MULTIPLE PARAMETERS USING CALL SYNTAX?
Hi Team,
I have a question about the stored procedure regarding the below code.
USE employees;
DROP PROCEDURE IF EXISTS emp_info;
DELIMITER $$
CREATE PROCEDURE emp_info( IN p_first_name VARCHAR(14), IN p_last_name VARCHAR(16), 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 ; My Question is how do you invoke the procedure using the CALL syntax. I know how to invoke it using the workbench schema. However, when I tried using the CALL function like this : CALL emp_info (Georgi, Facello), there's an error code 1318 Incorrect Number of Arguments. I can see that there're 3 arguments in the procedure, but what should I input in the third argument. Is the third argument @p_emp_no?If so, why is the third argument @p_emp_no?
DROP PROCEDURE IF EXISTS emp_info;
DELIMITER $$
CREATE PROCEDURE emp_info( IN p_first_name VARCHAR(14), IN p_last_name VARCHAR(16), 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 ; My Question is how do you invoke the procedure using the CALL syntax. I know how to invoke it using the workbench schema. However, when I tried using the CALL function like this : CALL emp_info (Georgi, Facello), there's an error code 1318 Incorrect Number of Arguments. I can see that there're 3 arguments in the procedure, but what should I input in the third argument. Is the third argument @p_emp_no?If so, why is the third argument @p_emp_no?
1 answers ( 0 marked as helpful)
Hi Vinh!
Thanks for reaching out.
The 3rd parameter is an OUT parameter - it contains the value you'll obtain after calling the procedure. Please feel free to revisit the following lecture or ask more questions for more information: https://learn.365datascience.com/courses/sql/stored-procedures-with-an-output-parameter
Hope this helps.
Best,
Martin