MySQL INSERT statement with CASE operation

Question

MySQL:

DELIMITER $$ CREATE DEFINER=`root`@`%` PROCEDURE `INSERT_Employee_Info`( IN lname varchar(64), IN fname varchar(64), IN mname varchar(64) ) BEGIN INSERT INTO tbl_employee_info(lname, fname, mname) VALUES(lname, fname, mname); END 

Sometimes the values ​​lname, fname and mname can contain only but not null, I want the insert statement to insert NULL instead of the value. ''

I had the following in mind, but I doubt very much that this will work:

 INSERT INTO tbl_employee_info(lname, fname, mname) VALUES(CASE lname WHEN '' THEN NULL ELSE lname END, CASE fname WHEN '' THEN NULL ELSE fname END, CASE mname WHEN '' THEN NULL ELSE mname END); 

Answer 1

it will work, but you need to put parentheses around the case

 INSERT INTO tbl_employee_info(lname, fname, mname) VALUES ( (CASE lname WHEN '' THEN NULL ELSE lname END), (CASE fname WHEN '' THEN NULL ELSE fname END), (CASE mname WHEN '' THEN NULL ELSE mname END) ); 

• SQLFiddle Demo ( IF Alternative Added)

Answer 2

You can use IF () -

 CREATE DEFINER=`root`@`%` PROCEDURE `INSERT_Employee_Info`( IN p_lname varchar(64), IN p_fname varchar(64), IN p_mname varchar(64) ) BEGIN INSERT INTO tbl_employee_info(lname, fname, mname) VALUES( IF(p_lname = '', NULL, p_lname), IF(p_fname = '', NULL, p_fname), IF(p_mname = '', NULL, p_mname)); END 

Another suggestion - do not use the same names for field names and parameters.