MySQL use variable in WHILE loop

Question

I can't seem to find why this gives me an error. It can't use the variable 'art' in the loop. But when I just do

 select art;

It gives me the correct number of items.

    drop procedure if exists bepaal_lijst_van_bij_te_bestellen_artikelen;

    delimiter //
    create procedure bepaal_lijst_van_bij_te_bestellen_artikelen()
    begin
        DECLARE art INT;
        DECLARE i INT;

        SELECT @art := COUNT(artikel_id) FROM artikelen;

        SET i = 1;
        WHILE i <= art DO

        END WHILE;
    end;
    //

    delimiter ;

it hurts the eyes to read this bepaal_lijst_van_bij_te_bestellen_artikelen! was there nothing simpler to choose? — mavili
– mavili, Commented Mar 16, 2013 at 15:55
yes there is. it hurts me too :)... but my university says I have to take this procedure name. I'm sorry — Jesse
– Jesse, Commented Mar 16, 2013 at 15:56

mavili · Accepted Answer · 2013-03-16 15:58:29Z

1

I think you should do

SET art := SELECT COUNT(artikel_id) FROM artikelen

answered Mar 16, 2013 at 15:58

mavili

3,4344 gold badges33 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jesse Over a year ago

This works now. SELECT @art := COUNT(artikel_id) FROM artikelen; SET doesn't work. However the while loop still doesn't want to fire up.

Kiwi · Accepted Answer · 2013-03-16 16:05:02Z

1

You could just do (note: I know how the assignment goes)

SELECT count(*) from Items into art;

answered Mar 16, 2013 at 16:05

Kiwi

2,7737 gold badges49 silver badges86 bronze badges

Comments

Jesse · Accepted Answer · 2013-03-16 16:08:36Z

1

Apparantly the while loop couldn't be empty and should contain something. fixed it by adding

SET i = 1 + i;

answered Mar 16, 2013 at 16:08

Jesse

5626 silver badges26 bronze badges

Collectives™ on Stack Overflow

MySQL use variable in WHILE loop

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related