1

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
  • Are you properly checking errors from mysql? Do you call the procedure from some other programming language or did you try mysql console too? – jkavalik Sep 13 '15 at 18:23
  • i am running it from the debug option inside dbforge. i am not sure what you mean by checking errors? – drenfro87 Sep 13 '15 at 19:07
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.