Stored procedure looping

[Ivan Smahin]

Hello Mr.,

Tuesday, December 11, 2007, 2:21:16 PM, you 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 ?

It should be: UNTIL counter >= 0

> 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

Fixed.

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

Sure, you can.




-- 
Best regards,
Ivan Smahin 
Senior Software Engineer
Paradigma Software, Inc
Valentina - The Ultra-Fast Database
http://www.valentina-db.com