Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: MySQL
Hi all

This is my stored procedure

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 10-Jan-13 23:14pm
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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

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:

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...
  Permalink  
Comments
bluesky 2013 at 11-Jan-13 4:51am
   
i have included the handler. Pls check my code
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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
//
  Permalink  

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

  Print Answers RSS
0 Sascha Lefévre 485
1 Sergey Alexandrovich Kryukov 325
2 Maciej Los 240
3 Abhinav S 174
4 Peter Leow 120
0 Sergey Alexandrovich Kryukov 6,773
1 OriginalGriff 6,311
2 Maciej Los 2,692
3 Peter Leow 2,654
4 Abhinav S 2,562


Advertise | Privacy | Mobile
Web01 | 2.8.150414.1 | Last Updated 11 Jan 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100