How to get rid of "Error 1329: No data - zero rows fetched, selected, or processed"

user_417784 0
Viewed 67416

I have a stored procedure which does not need to return any values. It runs smoothly and without any problem. However, it outputs an error message after finishing its run:

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

How can I get rid of this error message?

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
stored-procedures mysql sql
7 Answers

You need to define a continue handler like:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

So it would look like:

DECLARE done INT DEFAULT 0;
DECLARE l_name VARCHAR(20);
DECLARE my_cur CURSOR FOR
    SELECT name FROM customer_tbl;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

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;
user_5573550

I was getting the same error in my code, and I realized that I had not incremented my loop variable (using while loop) and hence, the loop was going infinite.

In your code too, you are not setting "done" to 1 anywhere, and I think the code is showing error because of that.

In the below code, instead of the variable "done", I have added a variable "count" that is initialized with the number of records in the table and is decremented after each insertion. The loop is terminated when count=0:

CREATE PROCEDURE `testing_proc`()  
READS SQL DATA  
BEGIN
  DECLARE count INT;
  DECLARE l_name VARCHAR(20);

  SELECT count(*) into count from customer_tbl;

  DECLARE my_cur CURSOR FOR
    SELECT name FROM customer_tbl;

  OPEN my_cur;
    my_cur_loop:
    LOOP FETCH my_cur INTO l_name;
    INSERT INTO names_tbl VALUES(l_name);
    SET count = count - 1;

    IF count = 0 THEN
            LEAVE my_cur_loop;
    END IF;

    END LOOP my_cur_loop;
  CLOSE my_cur;
END

I hope this helps!

user_131963910
Related