MySQL Forums
Forum List  »  Stored Procedures

Error Code:2014 Commands out of sync
Posted by: 克尔梅 李
Date: June 05, 2017 09:29PM

HI,
I am newbie of MySQL, I have created one MYSQL procedures, but it always have the following error: Error Code: 2014 Commands out of sync. I have attached the SQL procedure, any one has idea?


CREATE PROCEDURE `fill_carrier_change_load`()
BEGIN

DECLARE done INT DEFAULT FALSE;
DECLARE u_id INT;
DECLARE v_employer_id INT;
DECLARE v_employer_name CHAR(100);
DECLARE v_insuracne_company_id INT;
DECLARE v_insurance_company_name CHAR(100);
DECLARE v_active_flg CHAR(2);
DECLARE v_employer_payer_deactivation_date date;
DECLARE v_employer_payer_reactivation_date date;
DECLARE v_file_type CHAR(50);
DECLARE v_pbm CHAR(50);
DECLARE v_month_diff INT;
DECLARE v_counter INT;
DECLARE cursor_i CURSOR FOR select dq_employer_payer_status.*,TIMESTAMPDIFF(MONTH, employer_payer_deactivation_date, CASE when employer_payer_reactivation_date is null then curdate() else employer_payer_reactivation_date end
) as MONTH_DIFF from dq_employer_payer_status;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;



OPEN cursor_i;
read_loop: LOOP
FETCH cursor_i INTO u_id,v_employer_id,v_employer_name,v_insuracne_company_id,v_insurance_company_name,v_active_flg,v_employer_payer_deactivation_date,v_employer_payer_reactivation_date,v_file_type,v_pbm,v_month_diff;
IF done THEN
LEAVE read_loop;
END IF;
SET v_counter=0;
BEGIN
InsertCarrierChange: LOOP
IF v_counter<v_month_diff THEN

insert into dq_missing_claims_file_status_dev(employer_id,employer_name,insurance_company_name,insurance_company_id,missing_month_date,`status`,file_type,missing_year,missing_month,pbm,region)
values (v_employer_id,v_employer_name,v_insurance_company_name,v_insuracne_company_id, v_employer_payer_deactivation_date + interval v_counter month,'Carrier Change',v_file_type, YEAR(v_employer_payer_deactivation_date + interval v_counter month),MONTH(v_employer_payer_deactivation_date + interval v_counter month),NULL,'claims_master_prod');

SET v_counter=v_counter+1;
END IF;
LEAVE InsertCarrierChange;
END LOOP InsertCarrierChange;
END;

END LOOP;
CLOSE cursor_i;

END

Options: ReplyQuote


Subject
Views
Written By
Posted
Error Code:2014 Commands out of sync
5596
June 05, 2017 09:29PM


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.