DELIMITER $$ DROP PROCEDURE IF EXISTS `My_Proc`$$ CREATE DEFINER=`test`@`%` PROCEDURE `My_Proc`( IN reportId VARCHAR(20), IN SL VARCHAR(20), IN endDate DATETIME, IN bnk1 VARCHAR(20) ) MODIFIES SQL DATA BEGIN -- Declare local variables DECLARE V_SL,V_ACT_BAL,V_END_DATE VARCHAR(50) DEFAULT '0'; DECLARE V_AC2,V_CID,V_BRN VARCHAR(150) DEFAULT ' '; DECLARE done INT DEFAULT FALSE; DECLARE Sno INTEGER; DECLARE seqn_num INT DEFAULT 0; -- cursor declaration DECLARE bal_cursor CURSOR FOR SELECT cr.CID1,cr.AC2,cr.BLNC FROM XX cr,YY cu WHERE cr.CID1 =cu.CID1 AND cr.R_CRE_TIME <=CONCAT(SUBSTRING(endDate,1,11),'59:59:59.099') AND cr.bnk1 = bnk1 AND cr.BRN=SL; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; SELECT r.BRN INTO V_BRN FROM ZZ r WHERE r.SL_ID=SL; SET V_END_DATE = SUBSTRING(endDate,1,11); SET V_SL=SL; INSERT INTO REPORT_DUMP(report_id,report_data,seqn_num,R_CRE_ID,R_CRE_TIME) VALUES (reportId ,CONCAT('Sr.No|SL_ID|AVL_BAL|ACID|END_DATE|CID1|BRN_NAME'),seqn_num,'SYSTEM',NOW()); SET Sno=0; OPEN bal_cursor; read_loop_1: LOOP FETCH bal_cursor INTO V_CID1,V_ACID,V_ACT_BAL; IF done THEN LEAVE read_loop_1; END IF; SET seqn_num=seqn_num+1; SET Sno=Sno+1; INSERT INTO REPORT_DUMP(report_id,report_data,seqn_num,R_CRE_ID,R_CRE_TIME) VALUES ( reportId,CONCAT(Sno,'|',IFNULL(V_SL_ID,'-'),'|',IFNULL(V_ACT_BAL,'-'),'|',IFNULL(V_ACID,'-'),'|',IFNULL(V_END_DATE,'-'),'|',IFNULL(V_CID1,'-'),'|',IFNULL(V_BRN,'-')),seqn_num,'SYSTEM',NOW()); END LOOP; CLOSE bal_cursor; SELECT R.report_id,R.report_data FROM REPORT_DUMP R WHERE R.REPORT_ID = reportId ORDER BY R.seqn_num; DELETE FROM report_dump WHERE report_id = reportId; SET seqn_num=0; END$$ DELIMITER ;