Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

See the MySQL example on http://dev.mysql.com/doc/refman/5.5/en/loop.html: I copy it and paste on MySQL command line but it stop at every semicolon.

I wonder how to make these sentences work? I really don't know why.

mysql> CREATE PROCEDURE doiterate(p1 INT)
-> BEGIN
->   label1: LOOP
->     SET p1 = p1 + 1;

ERROR 1046 (3D000): No database selected
mysql>     IF p1 < 10 THEN
    ->       ITERATE label1;

ERROR 1064 (42000): You have an error in your SQL syntax; check the
                    manual that corresponds to your MySQL server
                    version for the right syntax to use near
   'IF p1 < 10 THEN
      ITERATE label1' at line 1
mysql>     END IF;
ERROR 1064 (42000): You have an error in your SQL syntax; check the
                    manual that corresponds to your MySQL server
                    version for the right syntax to use near
   'END IF' at line 1
.......
share|improve this question
2  
what is the full error message? –  Kuya John Dec 9 '12 at 15:49
add comment

migrated from stackoverflow.com Dec 9 '12 at 19:56

This question came from our site for professional and enthusiast programmers.

1 Answer

delimiter |
CREATE PROCEDURE doiterate(p1 INT)
BEGIN
  label1: LOOP
    SET p1 = p1 + 1;
    IF p1 < 10 THEN
      ITERATE label1;
    END IF;
    LEAVE label1;
  END LOOP label1;
END;
|
delimiter ;

If you don't set another delimiter than ; the statement will end at the first ; and your procedure definition will be incomplete. You need to tell MySQL that the stamentment should end at the delimiter you defined. After that you can set the delimiter back with delimiter ;

share|improve this answer
    
It works.All needed is a new delimiter..I can't thank you enough!! –  user1641513 Dec 9 '12 at 16:04
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.