Re: syntax error for simple IF - THEN

[Jassm Rahma]

this is the code followed by the error:

CREATE DEFINER=`root`@`%` PROCEDURE `sp_generate_payroll`(IN param_location int, IN param_month int, IN param_year int, IN param_created_user int)
BEGIN
DECLARE param_payroll_id INT;
DECLARE param_payroll_exists INT;
DECLARE param_salary_slip_id INT;
DECLARE param_employee_id INT;
DECLARE param_benefit_category INT;
DECLARE param_benefit_description VARCHAR(255);
DECLARE param_benefit_amount DECIMAL(11, 4);
DECLARE exit_loop BOOLEAN;

SET param_payroll_exists = (SELECT COUNT(*) FROM payroll WHERE location_id = param_location AND payroll_month = param_month AND payroll_year = param_year);

IF EXISTS (param_payroll_exists > 0) THEN
-- SET param_payroll_id = (SELECT payroll_id FROM payroll WHERE location_id = param_location AND payroll_month = param_month AND payroll_year = param_year);
SET param_payroll_id = 0;
END IF;

-- Declare the cursor
DECLARE cursor_employee_benefits CURSOR FOR
SELECT employee_id, benefit_category, benefit_description, benefit_amount FROM employee_benefits;

-- set exit_loop flag to true if there are no more rows
DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;

-- open the cursor
OPEN cursor_employee_benefits;

-- start looping
employee_loop: LOOP
FETCH cursor_employee_benefits INTO param_employee_id, param_benefit_category, param_benefit_description, param_benefit_amount;

-- check if the exit_loop flag has been set by mysql,
-- close the cursor and exit the loop if it has.
IF exit_loop THEN
CLOSE cursor_employee_benefits;
LEAVE employee_loop;
END IF;

-- generate salary slips;
-- IF EXISTS (SELECT salary_slip_id FROM salary_slips WHERE employee_id = param_employee_id AND slip_month = param_slip_month AND slip_year) THEN
-- BEGIN
-- SET param_salary_slip_id = (SELECT salary_slip_id FROM salary_slips WHERE employee_id = param_employee_id AND slip_month = param_slip_month AND slip_year);
-- END;
-- ELSE
-- BEGIN
-- END;
-- END IF;

INSERT INTO salary_slip_details (slip_details_description, slip_details_amount) VALUES (param_benefit_description, param_benefit_amount);
END LOOP employee_loop;

-- DECLARE param_salary_slip_id INT;

-- IF EXISTS (SELECT salary_slip_id FROM salary_slips WHERE slip_month = param_slip_month AND slip_year = param_slip_year) THEN
-- BEGIN
-- SET param_salary_slip_id = (SELECT salary_slip_id FROM salary_slips WHERE slip_month = param_slip_month AND slip_year = param_slip_yea);
-- END;
-- ELSE
-- BEGIN
-- INSERT INTO salary_slips () VALUES ()
-- END;
END



and this is the error:

You have an error in your SQL syntax. Check the manual that corresponds to you MySQL server for the right syntax to use near 'param_payroll_exists > 0) THEN ..........