MySQL Forums :: Stored Procedures :: syntax error for simple IF - THEN


Advanced Search

Re: syntax error for simple IF - THEN
Posted by: Jassm Rahma ()
Date: June 02, 2016 02:42AM

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 ..........

Options: ReplyQuote


Subject Views Written By Posted
syntax error for simple IF - THEN 3049 Jassm Rahma 06/01/2016 11:49AM
Re: syntax error for simple IF - THEN 366 Peter Brawley 06/01/2016 05:20PM
Re: syntax error for simple IF - THEN 393 Jassm Rahma 06/02/2016 12:41AM
Re: syntax error for simple IF - THEN 349 Peter Brawley 06/02/2016 02:15AM
Re: syntax error for simple IF - THEN 390 Peter Brawley 06/02/2016 07:38PM
Re: syntax error for simple IF - THEN 366 Jassm Rahma 06/02/2016 02:42AM
Re: syntax error for simple IF - THEN 331 Bryan Jones 06/03/2016 02:55PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.