How to Handle \"Error 1329: No Data - Zero Rows Fetched\" in MySQL Stored Procedures?

No Data Warning in Stored Procedures: "Error 1329: Zero Rows Fetched, Selected, or Processed"

In database programming, stored procedures are often used to encapsulate complex operations and ensure data integrity. However, sometimes these procedures may encounter errors or produce unexpected warnings. One common issue is the "Error 1329: No data - zero rows fetched, selected, or processed" message.

This warning typically appears when a stored procedure does not explicitly handle the case where no data is returned. To resolve this, one must properly handle the "NOT FOUND" condition. In MySQL, this can be achieved using a CONTINUE HANDLER statement.

Consider the following testing_proc stored procedure:

<code class="sql">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</code>

If this procedure runs without any data in the customer_tbl table, it will trigger the "Error 1329" warning. To prevent this, we can add the following CONTINUE HANDLER statement to the end of the procedure:

<code class="sql">DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;</code>

This statement tells MySQL to set the done variable to 1 if no rows are found. This will cause the cursor loop to exit, preventing the error from occurring.

Alternatively, one can also bypass the warning by adding a "dummy" statement at the end of the procedure that involves a table and is successful, such as:

<code class="sql">SELECT name INTO l_name FROM customer_tbl LIMIT 1;</code>

This statement will ensure that a row is fetched, clearing the warning.

By properly handling the "NOT FOUND" condition or adding a dummy statement, one can eliminate the "Error 1329" warning in stored procedures that do not return any data.

The above is the detailed content of How to Handle \"Error 1329: No Data - Zero Rows Fetched\" in MySQL Stored Procedures?. For more information, please follow other related articles on the PHP Chinese website!