How to Resolve \'Error 1329: No Data Fetched, Selected, or Processed\' in MySQL Stored Procedures?

How to Eliminate "Error 1329: No Data Fetched, Selected, or Processed"

When executing stored procedures that do not return values, users may encounter "Error 1329: No data - zero rows fetched, selected, or processed." To address this issue, follow these steps:

Consider the following stored procedure:

CREATE PROCEDURE `testing_proc`()  
    READS SQL DATA  
BEGIN  
    DECLARE done INT DEFAULT 0;
    DECLARE l_name VARCHAR(20);
    DECLARE my_cur CURSOR FOR
        SELECT name FROM customer_tbl;
    OPEN my_cur;
        my_cur_loop:
        LOOP FETCH my_cur INTO l_name;
            IF done = 1 THEN
                LEAVE my_cur_loop;
            END IF;
            INSERT INTO names_tbl VALUES(l_name);
        END LOOP my_cur_loop;
    CLOSE my_cur;
END

The error stems from MySQL's behavior of displaying a warning even when it has been handled. To prevent this, include the following line at the end of the procedure:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

Alternatively, add a "dummy" statement that involves a table and executes successfully after the loop, for example:

SELECT name INTO l_name FROM customer_tbl LIMIT 1;

This will clear the warning due to a bug/strange behavior in MySQL (referenced in http://dev.mysql.com/doc/refman/5.5/en/show-warnings.html).

The above is the detailed content of How to Resolve \'Error 1329: No Data Fetched, Selected, or Processed\' in MySQL Stored Procedures?. For more information, please follow other related articles on the PHP Chinese website!