How to increment a variable inside FOR loop in Stored procedure?

[Alex Kleyman]

Hi all,

I have a trivial stored procedure that needs to reorder data by changing displayorder field value.

CREATE PROCEDURE SVC.REORDER_DIVISIONS_ALPHA
(
 IN code VARCHAR(12),
 IN year INTEGER
)
LANGUAGE SQL P1:
BEGIN
    DECLARE counter INTEGER;
    SET counter = 1;
    FOR divisions AS 
       SELECT ID FROM SVC.DIVISIONS SV 
           WHERE   SV.LYEAR = year AND CODE = code 
           ORDER BY NAME ASC
 
       DO
         UPDATE SVC.DIVISIONS S SET S.DISPLAYORDER = counter WHERE S.ID = ID;
         SET counter = counter + 1;    
    END FOR;
 
END P1
@
 

I get result where all my displayorder fields are set to the same strange value of 292.

If I remove the line "SET counter = counter + 1;" I get all my values set to 1.

My goal is to have the values in the displayorder field to be 1,2,3,4,5 etc.

What am I missing?

THanks.

[Rabbit]

I don't know the DB2 sytnax but I'm pretty sure you meant to use S.ID = divisions and not S.ID = ID