How to Get Rid of "Error 1329: No Data - Zero Rows Fetched, Selected, or Processed"

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

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 ;

I am getting error #1329 - No data - zero rows fetched, selected, or processed in MySQL

You should a DECLARE HANDLER for the cursor when no more data is found: in docs you can find about how to do so.

Use

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement

Where handler type is one of: CONTINUE, EXIT or UNDO

Condition value is one of SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION | mysql_error_code. In this case NOT FOUND is what you're (maybe) looking for.

So your code would become something like this:

CREATE PROCEDURE cursorproc(OUT p_out DECIMAL(10,2)) 
BEGIN
   DECLARE l_salary, l_total DECIMAL(10,2);
   DECLARE _continue INT DEFAULT 0;

   DECLARE cur_1 CURSOR FOR SELECT line_distance FROM elements;   
   DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET _continue =1;
   OPEN cur_1;
   SET l_total = 0;
   REPEAT
      FETCH cur_1 INTO l_salary;
      SET l_total = l_total + l_salary;
      UNTIL _continue = 1;
   END REPEAT;
   CLOSE cur_1;
   SET p_out = l_total;
END;

Also review the docs for further information.

error 1329 (02000): no data - zero rows fetched, selected, or processed

This logic:

set i = 0;
select count(*) into cnt from student;
while i <= cnt do

is going through the loop 1 extra time. It is on the last trip through the loop that you are getting the error.

If cnt is 3, for instance, then the loops are:

0
1
2
3

You either want:

set i = 1

or alternatively

while i < cnt

In SQL, I would use the first method, because things usually start counting at 1 rather than 0 in SQL -- but they are equivalent.

Error 1329: No data - zero rows fetched, selected, or processed - Even when all is done right

I'm not sure what is causing this but changing your handler to the more specific SQL error might work in this case

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET updateDone = 1;

You might try to swith the if and the fetch

    IF updateDone =1 THEN
        LEAVE doUpdate;
    END IF;

    FETCH updater INTO Id, Elm, ElmParent, Type, Processed, Country;

this ensures that FETCH is not executed in the case the CONTINE HANDLER already signaled you're out of records.

solution at least found in here

Why does my stored procedure throw error 'No data - zero rows fetched, selected, or processed' when I have a WHERE in the statement?

you must define a continue handler:

DROP PROCEDURE IF EXISTS Test;
DELIMITER //

CREATE PROCEDURE Test()
BEGIN

    -- Our columns
    DECLARE name VARCHAR(45);
    DECLARE id INT DEFAULT -1;
    DECLARE done INT DEFAULT 0;
    -- Our cursor
    DECLARE cur CURSOR FOR SELECT name, id FROM MyTable WHERE id > 3;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    DROP TEMPORARY TABLE IF EXISTS TempTest;
    CREATE TEMPORARY TABLE TempTest
    (
    name VARCHAR(45) NOT NULL,
    id INT NOT NULL
    );

    -- Open our cursor
    open cur;

    -- Start our for loop
    forLoop: LOOP

    -- Get the row
    FETCH cur INTO name, id;
     IF done = 1 THEN
        LEAVE forLoop;
    END IF;
    INSERT INTO TempTest (name, id)
    VALUES ( name, id);

    END LOOP forLoop;

    -- Close the cursor
    CLOSE cur;

    -- NOW GET THE RESULTS
    SELECT * FROM TempTest;

END; //

DELIMITER ;

---

Related Topics