I have a stored procedure that is looping through a result set from a select statement. Inside of the loop there is a if statement that has two insert statements. When one of those insert statements runs for the first time i am kicked out of my while loop and the procedure ends. I have tried storing the inserts in another procedure and call that procedure from the inside the if statement and i have tried adding commit after the insert statements. Neither have works.
EDIT: OK i have found the issue. On the select statement at the beginning of the loop i have a select statement that is there to see if we run the insert statements. This statement will some times set a null value to the variable. this seems to be the reason that we are leaving the loop early. I have added this statement into the initial result set pull and it is working well now.
WHILE(curse = TRUE) do
fetch theTable into ciid,crid;
SELECT ClientInformation_CIID INTO classAttOrAb FROM clientmonitoringdata LEFT JOIN clientattendancemonitoring
ON FK_clientmonitoringdata_ClientMonitoringDataID = ClientMonitoringDataID
WHERE clientmonitoringdata.FK_clientrequirements_ClientRequirementsID = crid AND
EntryDate BETWEEN startDate and endDate AND VoidEntry = FALSE AND clientattendancemonitoring.AttendedClass IS NOT NULL LIMIT 1;
IF(classAttOrAb = 0) THEN
INSERT INTO clientmonitoringdata(ClientInformation_CIID,EntryDate,FK_clientrequirements_ClientRequirementsID)
VALUES( ciid, classDate, crid);
SET cmid = LAST_INSERT_ID();
INSERT INTO clientattendancemonitoring( FK_clientInformation_CIID, Absence, AttendedClass, ClassAttended, ExcusedAbsenceReason,
FK_clientmonitoringdata_ClientMonitoringDataID, FK_clientrequirements_ClientRequirementsID, AbsenceNotes, FK_fxuseraccounts_FXUserAccountsID)
VALUES(ciid,abType,FALSE,class,exAbReason,cmid,crid,abNotes,empId);
COMMIT;
end IF;
SET classAttOrAb = 0;
end while;
END
