I am experiencing an issue with a stored procedure I wrote some time ago that has recently stopped working. The SP populates a cursor and runs through a loop processing the data in another SP.
The issue is that since recreating the SP with a simple cosmetic change, the NOT FOUND Handler for the cursor always fires immediately as if the cursor were empty, and the SP returns a 1329 No data processed error.
However, running the select query that populates the cursor succesfully returns the data fine.
Here is the code:
DELIMITER // CREATE PROCEDURE `Import_Leave`() BEGIN DECLARE AbsenceID INT; DECLARE PersonID INT; DECLARE EmpID INT; DECLARE Start_Date DATETIME; DECLARE Numdays INT; DECLARE Category VARCHAR(50); DECLARE WorkFlow INT; DECLARE exit_loop BOOLEAN ; DECLARE absence CURSOR FOR SELECT AbsenceID,PersonID,EmpID,Start_Date,Numdays,Category,Workflow FROM staging_holidays WHERE EmpID is NOT NULL; DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE; OPEN absence; shoop_da_woop: LOOP #SELECT exit_loop; FETCH absence into AbsenceID,PersonID,EmpID,Start_Date,Numdays,Category,Workflow; #SELECT exit_loop; IF exit_loop THEN CLOSE absence; LEAVE shoop_da_woop; END IF; #Code to process the data goes here, but is never executed asthe cursor is coming back empty END LOOP shoop_da_woop; END// DELIMITER ;
I cannot figure out why this cursor is not fetching the data succesfuly when the underlying SELECT statement works fine. This SP used to work but has stopped working since the last time it was recreated. The commented out SELECT statements before and after the FETCH statement are in for debugging and they confirm that the NOT FOUND HANDLER is firing as soon as the FETCH completes.
Any help would be appreciated!