MySQL Routine INSERT Statement with CASE

insert, mysql

Solution

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)

Problem

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); ```

Original source