MySQL Routine INSERT Statement with CASE

Question

MySQL Routine:

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 may contain nothing but not null, I want the insert statement to insert NULL instead of the '' value.

I had the following in mind but I highly doubt it 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 parenthesis around 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 (added IF alternative)

Answer 2

You can use IF() function -

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

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