Stored procedure looping

[Mr.Bart Pietercil]

Not using repeat but using WHILE....DO

solves the problem with one (little) caveat.

When I run this procedure (which will make 101 + 1 records)
sql-editor reports 5152 records created in 0.007 seconds.

When I look in the data editor only 101 + 1 records are created.

Bart

PS: the WHILE DO example in the WIKI has the same problem as the  
REPEAT example

after END REPEAT or END WHILE does NOT come a ';'





On 11-dec-07, at 13:21, Mr. Bart Pietercil wrote:

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