Stored procedure looping

[Mr. Bart Pietercil]

Hi Ivan,

thx but


On 11-dec-07, at 13:42, Ivan Smahin wrote:

> 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

But I want my counter to insert values 100----->0 (inclusive) so it  
is UNTIL counter < 0

The repeat has to loop until counter is smaller than 0 ;

I modified my procedure and now it does what I want using WHILE:

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();
   WHILE counter > -1 do
   insert into tbl_score_levels 
(levelname,leveldescription,objptr_rs,ranking) values  
(counter,'--',mylastrecid,(100-counter)+1);
   SET counter = counter -1;
   END WHILE
   exception
   when others then
   call cm_CoreErrorHandler('cm_spBuildDefaultRatingSystem');
   end;

The same logic using repeat....until does not work. Something is  
wrong here I think UNLESS repeat...until means repeat....while ?


Bart Pietercil