Need Help with Stored Procedure

[ballouta]

Hello

I want to write a specific procedure as a Homework.

I started with simple procedure, without declaring and exit or leave or IF statements.

I wrote and created this procedure correctly:

delimiter //
create procedure customer_proc3 (IN cus_id INT(11), IN lname VARCHAR(50), IN fname VARCHAR(50), IN dobirth date, IN phone_num VARCHAR(20), OUT the_result INT)
BEGIN
Declare the_result INT default 0;
CASE cus_id
	when cus_id < 0 THEN SELECT 'The customer ID is less than Zero';
	when cus_id = 0 OR cus_id IS NULL THEN SELECT 'The customer IS is equal to zero';
	ELSE SELECT 'The Customer ID is smthg else';
END CASE;
END;
//

 

When I call it, i have an error:

mysql> call customer_proc3 (0,'John','Smith','1979-05-18','7687564', 5);

the error is:  1414 (42000): OUT or INOUT argument 6 for routine test.customer_proc3 is not a variable or NEW pseudo-variable in BEFORE trigger

 

If I call the procedure with only the five parameters, it also gives an error!

ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE test.customer_proc3; expected 6, got 5

 

WHY are both errors?

Thank you very much

[ignace]

It should be:

 

CREATE PROCEDURE customer_proc3 (IN cus_id INT(11), IN lname VARCHAR(50), IN fname VARCHAR(50), IN dobirth date, IN phone_num VARCHAR(20), IN the_result INT)

 

OUT is if you want to pass a variable (by-reference, not a constant thus like 5 or '2011-04-09') and the changes to be made visible to the caller, the initial value is NULL within the procedure unlike INOUT where the caller can set a value prior to passing it to the callee.

[ballouta]

Thank you ignace, this is very helpful i appreciate that