MySql handler for specific piece of code

Question

I am developing a stored procedure to transfer data from table to another. After normalizing existing database. I am reading all data from workassignment into cursor and inserting into table activities after replacing few columns with column relation ids. Code is below for this operation.

    DROP procedure IF EXISTS `transfer_work_assignment`;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `transfer_work_assignment`()
BEGIN

DECLARE val_emp_id,val_emp_name,val_task_name,val_sub_task_name,val_efforts,val_deliverables,val_sub_project_name VARCHAR(100);
DECLARE val_insert_date DATETIME;
DECLARE val_remarks TEXT;
DECLARE val_user_id,val_sub_task_id,val_sub_project_id,b,cnt INTEGER;
DECLARE curs1 CURSOR FOR SELECT emp_id,task_name,sub_task_name,efforts,deliverable,insert_date,sub_project_name,remarks FROM work_assignment limit 0,100;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET b=1;

OPEN curs1;
SET b=0;
SET cnt=0;

WHILE b=0 DO 

    FETCH curs1 INTO val_emp_id,val_task_name,val_sub_task_name,val_efforts,val_deliverables,val_insert_date,val_sub_project_name,val_remarks;
    BEGIN
        SELECT a.sub_task_id,a.sub_task_name,val_sub_task_name,b.task_name from pmtool_db.tbl_sub_task a,pmtool_db.tbl_task_details b where a.sub_task_name=val_sub_task_name and b.task_name=val_task_name;
    END;
    SET cnt=cnt+1;

END WHILE;


CLOSE curs1;

    SELECT cnt;

END$$
DELIMITER ;

Problem is I want to exit procedure after all values in cursor are completed fetching. But I want to continue executing select part inside while loop even, it has no values at some selections.

Hitesh Mundra · Accepted Answer · 2015-09-15 06:07:15Z

1

I think your store procedure needs to use continue handler or session variables. This could help you- By using CONTINUE HANDLER

DROP procedure IF EXISTS `transfer_work_assignment`;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `transfer_work_assignment`()
BEGIN

DECLARE nomore BOOLEAN DEFAULT FALSE;
DECLARE val_emp_id,val_emp_name,val_task_name,val_sub_task_name,val_efforts,val_deliverables,val_sub_project_name VARCHAR(100);
DECLARE val_insert_date DATETIME;
DECLARE val_remarks TEXT;
DECLARE val_user_id,val_sub_task_id,val_sub_project_id,b,cnt INTEGER;
DECLARE curs1 CURSOR FOR SELECT emp_id,task_name,sub_task_name,efforts,deliverable,insert_date,sub_project_name,remarks FROM work_assignment limit 0,100;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET nomore = TRUE;

OPEN curs1;
SET b=0;
SET cnt=0;

myloop: LOOP 

    FETCH curs1 INTO val_emp_id,val_task_name,val_sub_task_name,val_efforts,val_deliverables,val_insert_date,val_sub_project_name,val_remarks;
    IF NOT nomore THEN   
        SELECT a.sub_task_id,a.sub_task_name,val_sub_task_name,b.task_name from pmtool_db.tbl_sub_task a,pmtool_db.tbl_task_details b where a.sub_task_name=val_sub_task_name and b.task_name=val_task_name;
        SET nomore = FALSE; 
    END IF;  
    SET cnt=cnt+1;
    IF nomore THEN
            LEAVE myloop;
    END IF;
END LOOP myloop;


CLOSE curs1;

SELECT cnt;

END$$
DELIMITER ;

Here is my example-

drop procedure if exists cur;
delimiter $$
create procedure cur()
BEGIN 
     DECLARE rowcount INT default 0;
     DECLARE code_var VARCHAR(320); 
     DECLARE name_var VARCHAR(320);
     DECLARE myvar VARCHAR(320); 
     DECLARE nomore BOOLEAN DEFAULT FALSE;
     DECLARE c cursor FOR
       SELECT code,name FROM Country WHERE continent='Africa';
     open c;
     begin

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET nomore = TRUE;
        myloop: loop
              fetch c into code_var,name_var; 
                IF NOT nomore THEN  
                   select name into myvar from City where countrycode='ggg' limit 1;    
                   select myvar; 
                   SET nomore = FALSE;      
                END IF;    
              select code_var, name_var;
          set rowcount=rowcount+1;
         IF nomore THEN
            LEAVE myloop;
         END IF;
         end loop myloop;
     end;  
     close c;
     select rowcount as 'no of country rows fetched';
     select rowcount;
END $$
delimiter ;
call cur();

By using SESSION VARIABLES

DROP procedure IF EXISTS `transfer_work_assignment`;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `transfer_work_assignment`()
BEGIN

DECLARE val_emp_id,val_emp_name,val_task_name,val_sub_task_name,val_efforts,val_deliverables,val_sub_project_name VARCHAR(100);
DECLARE val_insert_date DATETIME;
DECLARE val_remarks TEXT;
DECLARE val_user_id,val_sub_task_id,val_sub_project_id,b,cnt INTEGER;
DECLARE curs1 CURSOR FOR SELECT emp_id,task_name,sub_task_name,efforts,deliverable,insert_date,sub_project_name,remarks FROM work_assignment limit 0,100;

OPEN curs1;
SET cnt=0;

BEGIN
    DECLARE EXIT HANDLER FOR SQLSTATE '02000' BEGIN END;
    LOOP
          FETCH curs1 INTO val_emp_id,val_task_name,val_sub_task_name,val_efforts,val_deliverables,val_insert_date,val_sub_project_name,val_remarks;
          SELECT @var1 := a.sub_task_id, @var2 := a.sub_task_name, @var3 := val_sub_task_name, @var4 := b.task_name from pmtool_db.tbl_sub_task a,pmtool_db.tbl_task_details b where a.sub_task_name=val_sub_task_name and b.task_name=val_task_name;
          SET cnt=cnt+1;
    END LOOP;
END;


CLOSE curs1;

SELECT cnt;

END$$
DELIMITER ;

I have not your tables. But below is my code which running fine. It will run until the all records of cursor not fetched even if select with in loop doesn't select the record it doesn't stop. First select with in loop doesn't select any record and @myvar set as null. It doesn't terminate the loop. Second select with in loop have always select record, if it fails to select it will terminate the loop. So the thing is use @variable

drop procedure if exists cur;
delimiter $$
create procedure cur()
BEGIN 
     DECLARE rowcount INT default 0;
     DECLARE code_var VARCHAR(320); 
     DECLARE name_var VARCHAR(320);
     DECLARE myvar VARCHAR(320); 
     DECLARE c cursor FOR
       SELECT code,name FROM Country WHERE continent='Africa';
     open c;
     begin
        declare EXIT HANDLER for SQLSTATE '02000' begin end;    
         loop
              fetch c into code_var,name_var;
              select @myvar := name from City where countrycode='ggg';
              select name into myvar from City where countrycode=code_var limit 1;         
              select code_var, name_var;
              select myvar, @myvar;
          set rowcount=rowcount+1;
        end loop ;
     end;  
     close c;
     select rowcount as 'no of country rows fetched';
     select rowcount;
END $$
delimiter ;
call cur();

edited Sep 15, 2015 at 6:07

answered Sep 10, 2015 at 10:48

Hitesh Mundra

1,5731 gold badge10 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

akhil Over a year ago

i don't want to exit from procedure i want procedure to keep executing even though select returns empty after fetch until fetch gets emptied

Hitesh Mundra Over a year ago

@akhil As you told, you want to exit after the fetch all record from cursor. I am not understanding what you want ?

akhil Over a year ago

I want procedure to exit only after cursor values are completed and not when other select return no rows

Hitesh Mundra Over a year ago

@akhil my code running even if select with in cursor is not return any record. if i am missing something then reply

akhil Over a year ago

this is working fine as it is but when I am using select column into var from table if there are no records procedure exits

|

Collectives™ on Stack Overflow

MySql handler for specific piece of code

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related