question

I have created this Stored Procedure, which is working fine, but the only thing is i am using Cursors here, and looping it. It only fetches one Record, instead of many. Can anyone throw some light and let me know as to where i am going wrong, so that it fetches more records instead of one. The select statement fetches around 50 k records. DROP PROCEDURE IF EXISTS SP_HH; CREATE PROCEDURE "SP_HH"() BEGIN DECLARE M_SK INT; DECLARE Line_1 VARCHAR(50); DECLARE Line_2 VARCHAR(50); DECLARE SUITE VARCHAR(50); DECLARE City VARCHAR(35); DECLARE St_Prov_cd VARCHAR(4); DECLARE Postal_cd VARCHAR(10); DECLARE cntry_cd VARCHAR(3); DECLARE Addr_key VARCHAR(40); DECLARE Mat_Par INT; DECLARE House INT; DECLARE done INT DEFAULT FALSE; DECLARE a_curr CURSOR FOR SELECT A.PARTY, LINE_1, SUITE, CITY, ST_PROV_CD, POSTAL_CD, CNTRY_CD, MD_DR_KEY FROM TABLE1 A, TABLE2 B WHERE A.P = B.P AND B.P = B.B_ID order by m_a_key limit 100; DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = TRUE; `OPEN a_curr; REPEAT read_loop: LOOP FETCH addr_curr INTO p_M, Line_1, SUITE, City, St_Prov_cd, Postal_cd, cntry_cd, Addr_key; UNTIL IF done THEN LEAVE read_loop; END IF; SET M_SK = NULL; SELECT A.P_SK , C.HH_SK INTO M_SK, H_SK FROM TABLE1 a, TABLE2 b, TABLE3 c WHERE A.P_SK = B.P_SK AND B.P_SK = B.BUSN_ID AND A.LINE_1 = Line_1 ; IF M_SK IS NOT NULL THEN INSERT INTO TABLE (HH_SK, I_SK, HH_EFF_DT, HH_EXP_DT) VALUES (H_SK, M_SK, NOW(), '9999-12-31'); ELSE INSERT INTO TABLEX (HH_NM, INS_USR_ID, INS_DT, UPD_USR_ID, UPD_DT) VALUES (ifnull(p_A_key,-1), 'User', NOW(), 'User', NOW()); END IF; END LOOP; CLOSE a_curr; END;