Stored procedure looping

[Mr. Bart Pietercil]

Hi List,

I don't see what is wrong with this, so maybe a couple more eyes can  
help me out ?


  create or replace procedure cm_spBuildDefaultRatingSystem()
   begin
   DECLARE mylastrecid Long;
   DECLARE counter INT;
   SET counter = 100;
   insert into tbl_rating_systems(rs_name,rs_description,creation_dt)  
values ('cm_spBuildDefaultRatingSystem','score system generated by  
CoreManager',NOW());
   set mylastrecid = select Last_RecID();
   REPEAT
   insert into tbl_score_levels 
(levelname,leveldescription,objptr_rs,ranking) values  
(counter,'--',mylastrecid,(100-counter)+1);
   SET counter = counter -1;
   UNTIL counter < 0
   END repeat
   exception
   when others then
   call cm_CoreErrorHandler('cm_spBuildDefaultRatingSystem');
   end;

This procedure is accepted and when run creates 2 records
1) 1 record in tbl_rating_systems--> perfect
2) 1 record in tbl_score_levels--> unexpected result it seems the  
loop runs only once. Why ?

clearly something is wrong in the repeat

when looking at the WIKI I found a couple of things different:


Example:

CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
     SET @x = 0;

     REPEAT
        SET @x = @x + 1;
        UNTIL @x > p1
     END REPEAT;
END


1)END REPEAT; --> when trying this the sqlparser gives an error on the ;
so probably the example is wrong


2) in the example an @-variable is used (@x)

However in other examples on the same page (While-loop)

CREATE PROCEDURE dowhile()
BEGIN
   DECLARE v1 INT DEFAULT 5;

   WHILE v1 > 0 DO
     ...
     SET v1 = v1 - 1;
   END WHILE;
END

a local scope variable is declared and used; so I suppose I can use  
local scope variables ?

Any ideas ?

TIA

Bart Pietercil