stored procedures - Mysql Error Code: 1329. No data - zero rows fetched, selected, or processed

Question

so i have a store procedure where the function is to find out the column which contains the name expired or end (Datetime type), and after that find out the record for that column which containing 90 days away from now. so I made this store procedure

CREATE DEFINER=`fachry`@`%` PROCEDURE `get_data`()
BEGIN
DECLARE i VARCHAR(100);
DECLARE a VARCHAR(100); 
   DECLARE cur1 CURSOR FOR SELECT DISTINCT 
   TABLE_NAME, COLUMN_NAME FROM information_schema.columns WHERE (COLUMN_NAME LIKE 'End%' OR COLUMN_NAME LIKE 'Expired%') AND TABLE_SCHEMA='brambang_uom'
  AND TABLE_NAME NOT LIKE 'discount%' ;
   OPEN cur1;

   read_loop: LOOP
       FETCH cur1 INTO i,a;
    
       -- SELECT i,a; -- printing table name
    
       SET @s = CONCAT('select * from ', i, ' where ', a, ' >= CURDATE() + INTERVAL 90 DAY AND ' , a, ' <
       CURDATE() + INTERVAL 91 DAY'); 
        PREPARE stmt1 FROM @s;
        EXECUTE stmt1;
        DEALLOCATE PREPARE stmt1;

   END LOOP read_loop;

   CLOSE cur1;



END

but idk why it keeps on error

Error Code: 1329. No data - zero rows fetched, selected or processed

question from:https://stackoverflow.com/questions/65557447/mysql-error-code-1329-no-data-zero-rows-fetched-selected-or-processed

Answer 1

You have not declared a handler -

drop procedure if exists p;
delimiter $$
CREATE  PROCEDURE p()
BEGIN
DECLARE i VARCHAR(100);
DECLARE a VARCHAR(100); 
    declare finished int default 1;
   DECLARE cur1 CURSOR FOR 
   SELECT TABLE_NAME, COLUMN_NAME 
    FROM information_schema.columns 
    WHERE (COLUMN_NAME LIKE 'End%' OR COLUMN_NAME LIKE 'Expired%') 
            AND TABLE_SCHEMA='sandbox'
           AND TABLE_NAME NOT LIKE 'discount%' ;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET FINISHED = 0;
    
    OPEN cur1;

   read_loop: LOOP
     IF FINISHED = 0 THEN
            LEAVE read_loop;
        END IF;
        
       FETCH cur1 INTO i,a;
    
       #SELECT i,a; -- printing table name
    
       SET @s = CONCAT('select * from ', i, ' where ', a, ' >= CURDATE() + INTERVAL 90 DAY AND ' , a, ' <
       CURDATE() + INTERVAL 91 DAY'); 
       PREPARE stmt1 FROM @s;
       EXECUTE stmt1;
       DEALLOCATE PREPARE stmt1;
       
       #select @s;

   END LOOP read_loop;

   CLOSE cur1;

END $$

delimiter ;