mySql while loop ending after a insert

Question

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

• jkavalik: Are you properly checking errors from mysql? Do you call the procedure from some other programming language or did you try mysql console too?
• drenfro87: i am running it from the debug option inside dbforge. i am not sure what you mean by checking errors?

Answer 1

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.