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 George Jonsson 215
1 Kornfeld Eliyahu Peter 169
2 Zoltán Zörgő 139
3 PIEBALDconsult 130
4 OriginalGriff 120
0 OriginalGriff 6,165
1 DamithSL 4,658
2 Maciej Los 4,107
3 Kornfeld Eliyahu Peter 3,649
4 Sergey Alexandrovich Kryukov 3,342


Advertise | Privacy | Mobile
Web01 | 2.8.141220.1 | Last Updated 11 Jan 2013
Copyright © CodeProject, 1999-2014
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