SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2007
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Why does my while loop loop only 2 times instead of 10?

    Hi Guys,

    Here's my table data and my stored procedure in mySql
    Table checkval
    Code:
    val
    ----
    5705
    5704
    5703
    5702
    5701
    Stored Procedure

    Code:
    BEGIN
    DECLARE x INT;
    DECLARE done INT DEFAULT FALSE;
    DECLARE myType INT;
    DECLARE cur1 CURSOR FOR select val from checkval;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
     
    SET x = 10;
     
    WHILE x  >= 2 DO
    ##############
     
    OPEN cur1;
     
    read_loop: LOOP
    IF done THEN
    LEAVE read_loop;
    END IF;
     
    FETCH cur1 INTO myType;
    insert into myType values (myType);
     
     
    END LOOP read_loop;                 
    CLOSE cur1;
    SET  x = x-1; 
     
    ##################
    END WHILE;
     
    END
    Code:
    Current Output
    5705
    5704
    5703
    5702
    5701--Loops only twice
    5705
    5704
    5703
    5702
    5701
    Code:
    Desired Output
    5705 - Loops 8 times
    5704
    5703
    5702
    5701
    5705
    5704
    5703
    5702
    5701
    5705
    5704
    5703
    5702
    5701
    5705
    5704
    5703
    5702
    5701
    5705
    5704
    5703
    5702
    5701
    5705
    5704
    5703
    5702
    5701
    5705
    5704
    5703
    5702
    5701
    5705
    5704
    5703
    5702
    5701
    In all of this, even when I specify a while loop starting at `10` and ending at `2`, why do I get my output only looped twice in the target table. I was hoping to see it repeated 8 times. Can you pls help me on this? I'm backward with stored procedures, so I guess I'm not putting something in the right place or something like that.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    why bother writing a stored proc?

    it's faster and a lot simpler to join your checkval table with a numbers table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •