MySQL: Combining all results in a loop (possibly using UNION?)

Question

Edit: Already solved and answered: But if you have a similar problem, feel free to read and see how the process works

background I created a procedure called "sea_dew_potion" and placed it inside a loop called "loopseadew". The loop works as expected.

question My loop produces 18 results, but I want it to be in a single result set so that I can export it to tableau to make a line chart with multiple data points, thus forming some form of curve. After some research I think UNION could work but I'm not sure how to do it, mainly because I'm just figuring out how the loop works and then how to install another process inside the loop haha. thanks for your help!

DELIMITER $$
CREATE PROCEDURE loopseadew(IN p_base_name VARCHAR(255), IN p_base_amount integer, IN p_ing_name VARCHAR(255), p_ing_amount integer, p_pot_units INTEGER(3))
BEGIN
    DECLARE p_ing_amount int;
    
    SET p_ing_amount = 0;
    
    loop1: LOOP
        SET p_ing_amount = p_ing_amount + 1;
        CALL sea_dew_potion('sea dew', 100, 'skadite', 0, 10);
        IF p_ing_amount = 18 THEN
            LEAVE loop1;
        END IF;
    END LOOP loop1;
    SELECT p_base_name, p_base_amount, p_ing_name, p_ing_amount,
        m.price * p_ing_amount AS cost
    FROM
        multiplier m
    WHERE
        m.ing_name = p_ing_name;
END$$
DELIMITER ;

EDIT: I've made some progress, but I'm getting null values ​​for the "heal" and "cost" columns. Treatment values ​​are generated by the program "sea_dew_potion". Now I have 18 individual results where all the values ​​are correct, and one result which is a combination of all results but with empty values ​​for treatment and cost. The screenshot at the bottom shows the issue between result sets.

DELIMITER $$
CREATE PROCEDURE allloopseadew(IN p_base_name VARCHAR(255), IN p_base_amount integer, IN p_ing_name VARCHAR(255), p_ing_amount integer, p_pot_units INTEGER(3))
BEGIN
    DECLARE x int;
    CREATE TEMPORARY TABLE allresults (p_base_name varchar(255), p_base_amount int(3), p_ing_name VARCHAR(255), p_ing_amount int(3), heal decimal(10,2), cost decimal(10,2));
    
    SET x = 0;
   
    loop1: LOOP
        SET x = x + 1;
        CALL sea_dew_potion('sea dew', 100, 'skadite', x, 10);
        IF x = 18 THEN
            LEAVE loop1;
        END IF;
        INSERT INTO allresults
        VALUES(p_base_name, p_base_amount, p_ing_name , x, heal, cost);
    END LOOP loop1;
    SELECT p_base_name, p_base_amount, p_ing_name, p_ing_amount,
        m.price * p_ing_amount AS cost
    FROM
        multiplier m
    WHERE
        m.ing_name = p_ing_name;
    SELECT * FROM allresults;
END$$
DELIMITER ;

Edit Part 2 Shout out to @blabla_bingo, I finally figured it out! This is the final query for anyone interested haha

CREATE PROCEDURE allloopseadew(IN p_base_name VARCHAR(255), IN p_base_amount integer, IN p_ing_name VARCHAR(255), p_ing_amount integer, p_pot_units INTEGER(3))
BEGIN
    DECLARE x decimal(10,3);
    DECLARE v_heal decimal(10,3);
    DECLARE v_cost decimal(10,3);
    DECLaRE v_total_amount int(3);
    CREATE TEMPORARY TABLE allresults (p_base_name varchar(255), p_base_amount int(3), p_ing_name VARCHAR(255), p_ing_amount int(3), heal decimal(10,2), cost decimal(10,2));
    
    SET x = 0;
   
    loop1: LOOP
        SET x = x + 1;
        SET v_total_amount = 
            (SELECT p_base_amount + x);
        SET v_heal = 
            (SELECT 
                round(max((2*(2.1*(p_base_amount/v_total_amount))*1*(1+sqrt(x/v_total_amount)*m.multiplier_value)*p_pot_units)),3)
            FROM
                multiplier m
            WHERE
                m.ing_name = p_ing_name);
        SET v_cost = 
            (SELECT 
                m.price * x
            FROM
                multiplier m
            WHERE
                m.ing_name = p_ing_name);
        CALL sea_dew_potion('sea dew', 100, 'skadite', x, 10);
        IF x = 18 THEN
            LEAVE loop1;
        END IF;
        
        INSERT INTO allresults
        VALUES(p_base_name, p_base_amount, p_ing_name, x, v_heal, v_cost);
    END LOOP loop1;
    SELECT p_base_name, p_base_amount, p_ing_name, p_ing_amount,
        m.price * p_ing_amount AS cost
    FROM
        multiplier m
    WHERE
        m.ing_name = p_ing_name;
    SELECT * FROM allresults;
END$$
DELIMITER ;

All results have no cure or cost

Personal Outcomes vs. Treatment and Costs

Final correct query results

Answer 1

UNION has its drawbacks, such as the inability to sort using the ORDER BY clause. Additionally, if you decide to continue, you would need to use a total of 17 UNION in this example, making the query too lengthy. I recommend using a results table and inserting your program sea_dew_potion into it. You can then query the resulting table and possibly make some adjustments before exporting to the tableau table. Finally, as a side note, instead of calling sea_dew_potion 18 times in a loop, we could add a count parameter to the sea_dew_potion procedure so that we only have to define the count and run the procedure once. For example sea_dew_potion(p1,p2,p3,p4,p5,count_num int).