Mysql – Error in IF statement in MySQL

MySQL

I am wondering why I am getting error when running below simple IF in MySQL?

IF NOT EXISTS (SELECT employee_attendance_id FROM employee_attendance WHERE employee_number = 10 AND attendance_date_time = '2015-10-11 13:14:16') THEN
BEGIN
    INSERT IGNORE INTO employee_attendance (employee_number, attendance_date_time)  VALUES (10, '2015-10-11 13:14:16');
END;
END IF;

This is the error I am getting:

[SQL] IF NOT EXISTS (SELECT employee_attendance_id FROM
employee_attendance WHERE employee_number = 10 AND
attendance_date_time = '2015-10-11 13:14:16') THEN BEGIN
INSERT IGNORE INTO employee_attendance (employee_number, attendance_date_time) VALUES (10, '2015-10-11 13:14:16'); [Err] 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 'IF NOT EXISTS (SELECT employee_attendance_id FROM
employee_attendance WHERE empl' at line 1

Thanks,
Jassim

Best Answer

You cannot execute such statements unless you use a stored proc: You can run this below. It should work.

DROP procedure IF EXISTS `test2`;
DELIMITER $$
CREATE  PROCEDURE `test2`(p_employee_number int, p_date datetime)
Begin

if  NOT EXISTS (SELECT 1 
    FROM employee_attendance 
        WHERE employee_number =p_employee_number  AND attendance_date_time = p_date) THEN

    INSERT IGNORE INTO employee_attendance (employee_number, attendance_date_time)  VALUES (p_employee_number, p_date);

End if ;
END$$

DELIMITER ;
Related Question