In my last tip i have explained “How to use a cursor in MYSQL to loop through a result set”. We use cursor to execute a set of tasks on a result set. we know that the continue handler updates the ‘done’ variable to 1 when a ‘Not Found’ error occurs. Imagine a condition where the set of tasks include a ‘SELECT INTO’ statement.
SELECT company_id INTO comID
FROM users
WHERE user_id = userID;
If one of the executions of the ‘SELECT INTO’ statement doesn’t get any record then ‘Not Found’ error occurs and the continue handler will update the done variable and the execution will stop for the next record in the result set. To avoid this type of error we can check if the select statement found a record or not. if not then we can reset the ‘done’ variable, so the loop will continue.
IF comID IS NULL THEN
done = 0;
END IF;
This way it will execute the set of tasks for all the records in the result set.