How to use loops in MySQL stored procedure

Example 1

Here’s a stored procedure that loops through the numbers 1 to 5 and returns their sum:

CREATE PROCEDURE sum_nums_1_to_5()
BEGIN
    DECLARE sum INT DEFAULT 0;
    DECLARE counter INT DEFAULT 0;

    my_loop:
    LOOP
        # Initialize counter
        SET counter = counter + 1;

        # If counter is greater than 5, leave the loop
        IF counter > 5 THEN
            LEAVE my_loop;
        END IF;

        SET sum = sum + counter;
    END LOOP my_loop;

    SELECT sum;
END;

Call loop:

CALL sum_nums_1_to_5

Example 2

create procedure add_user_to_all_vetted_companies(IN user_id int)
begin
    declare done int default 0;
    declare company_id int;
    declare company_ids varchar(255) default '';
    declare cur cursor for select id from company where is_vetted = 1 order by name;
    declare company_user_id int;
    declare continue handler for not found set done = 1;

    open cur;

    repeat
        fetch cur into company_id;
        if not done then
            set company_ids = concat(company_ids, company_id, ',');
            insert into company_user (user_id, company_id, notify, created_at) values (user_id, company_id, 1, now());

            set company_user_id = last_insert_id();

            insert into company_user_role (company_user_id, role_id) values (company_user_id, 2);
            insert into company_user_role_selected (company_user_id, role_id) values (company_user_id, 2);
        end if;
    until done end repeat;
    close cur;

    select company_ids;
end;