MySQL Forums
Forum List  »  Stored Procedures

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
3092
June 01, 2016 11:49AM
Re: syntax error for simple IF - THEN
386
June 02, 2016 02:42AM


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.