Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi all

This is my stored procedure

SQL
delimiter //
CREATE PROCEDURE cursorproc() 
BEGIN
   DECLARE n int;
   DECLARE _continue INT DEFAULT 0;
   DECLARE cur_1 CURSOR FOR SELECT n1 FROM num;   
   DECLARE exit HANDLER FOR NOT FOUND
    SET _continue =1;
   OPEN cur_1;
   
   REPEAT
      FETCH cur_1 INTO n;
		insert into t1 values(n);
      UNTIL _continue = 1
   END REPEAT;
   CLOSE cur_1;
   
END
//


values got inserted into the table but getting warning as follows

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

Hepl me to solve this. Thanks in advance
Posted

I guess you just forgot to include the following line in your post:

SQL
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;


Your code is correct, but bug/strange behaviour of mysql causes the warning to appear even if it was handled. You can avoid that if you add a "dummy" statement to the end of your procedure that invovles a table and is successful, this will clear the warning. (See how warnings) In your case:

SQL
SELECT name INTO l_name FROM customer_tbl LIMIT 1;


after the end of the loop. On MySQL 5.5.13 the warning disappears, on Linux and Windows. I commented on MySQL Bug 60840 and I hope they will fix it some time in the future...
 
Share this answer
 
Comments
bluesky 2013 11-Jan-13 4:51am    
i have included the handler. Pls check my code
SQL
delimiter //

CREATE PROCEDURE cursorproc()
BEGIN
   DECLARE n int;
   DECLARE _continue INT DEFAULT 0;

   DECLARE cur_1 CURSOR FOR SELECT n1 FROM num;
   DECLARE continue HANDLER FOR NOT FOUND
    SET _continue =1;
   OPEN cur_1;

   l1:loop
      FETCH cur_1 INTO n;
        if _continue!=1 then
            insert into t1 values(n);

        else
            leave l1;
    end if;
   END loop;

  close cur_1;

END
//
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900