Mysql – Error in stored procedure on Phptheadmin

MySQLphpmyadminstored-procedures

I have created this simple StoredProcedure in the Phpmyadmin SQL console but I keep getting syntax errors. I have looked at similar created StoredProcedures but can't seem to figure out what the error is. Here is my StoredProcedure:

DELIMITER //
DROP PROCEDURE IF EXISTS addnewemployee //
CREATE PROCEDURE addnewemployee 
(
IN eno INT(11),
IN ebdate date,
IN efname VARCHAR(14),
IN elname VARCHAR(16),
IN egender enum,
IN ehdate date
)
BEGIN
  INSERT INTO employees(emp_no, birth_date, first_name, last_name, gender, hire_date) 
  values (eno, ebdate, efname, elname, egender, ehdate);
END//
DELIMITER ;

And this is the error message seen in Phpmyadmin sql console:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',
IN ehdate date
)
BEGIN
  INSERT INTO employees(eno, ebdate, efname, elname' at line 7

Best Answer

Change

IN egender enum,

to

IN egender VARCHAR(99),

That will let you pass a string into the prodecure, then when doing the INSERT, the string will be acceptable to the ENUM column.

An analogy -- You don't have to be precise about numeric (TINYINT/BIGINT/FLOAT/DECIMAL, or even a string) when you pass it into a procedure. What matters is whether the context (INSERT in your case) can convert it as needed.

Note, as usual, that 1064 pointed 'exactly' at the bad spot -- either the comma or what was immediately before it. In this case enum ease expecting (...), so it was surprised by the comma.