mysql stored procedure template for defining a cursor and do error handling

This article will define a basic blueprint for a mysql stored procedure that will declare a cursor to fetch employee details of a particular department and log them into a separate table.

The stored procedure will also handle any error occurred during the script execution. The behavior is to rollback the work done and exit from the procedure.

SET @sql_notes = @@session.sql_notes;

SET SESSION sql_notes = 0;

DROP PROCEDURE IF EXISTS usp_fetch_and_log_emp_name;

DELIMITER $$

CREATE PROCEDURE usp_fetch_and_log_emp_name(IN p_deptid INT)
BEGIN
 declare v_empid INT;
 declare v_empname VARCHAR(100);
 declare v_deptid INT;
 declare no_more_emp boolean;
 
 declare emp_cursor cursor for
  select emp.empid, emp.name, emp.deptid
  from employees emp
  where emp.deptid = p_deptid;
 
 declare exit handler for sqlexception
  begin
   rollback;
   close emp_cursor;
   select @@warning_count as warning_count;
   show errors;
  end;

 declare exit handler for sqlwarning
  begin
   rollback;
   close emp_cursor;
   select @@warning_count as warning_count;
   show warnings;
  end;
  
 declare continue handler for not found
 begin
  set no_more_emp = true;
  SELECT 1 INTO @handler_invoked FROM (SELECT 1) AS t;
 end;

 start transaction;
 
  open emp_cursor;
  
  emp_loop: loop

   fetch emp_cursor
   into v_empid, v_empname, v_deptid;

   if no_more_emp then
    leave emp_loop;
   end if;

   INSERT INTO emp_log(empid, name, deptid) VALUES(v_empid, v_empname, v_deptid);
   
  end loop emp_loop;
  
  close emp_cursor;
  
 commit;

END
$$

DELIMITER ;

CALL usp_fetch_and_log_emp_name(5);

DROP PROCEDURE IF EXISTS usp_fetch_and_log_emp_name;

SET SESSION sql_notes = @sql_notes;

Hope this will help.