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