CODEWITHSUNDEEP
mysqlstored-procedures
Wooopsa
Wooopsa

Reputation: 330

Insert each row of stored procedure into a table

I have two stored procedures in MySql. One of them does the calculation and returns everything in groups. With another stored procedure, I have a WHILE statement that runs for each month and calls the first stored procedure. I want to get the result of each group and save them in a table.

The code is something like:

CREATE PROCEDURE `first`(IN `start` date, **it should be list of items** )
begin 
    SET result = 0;
    
SELECT 
    item.name,
    sum(amount) *INTO result*

FROM
    FOO
        INNER JOIN
    BAR ON id = id
WHERE
        date(someDate) < date(start) 
group by something;
end

And the runner is something like:

CREATE PROCEDURE `runner`(IN `from` date, IN `to` date)
BEGIN
    set dateTo = date(to);

    set dateFrom = date(from);

    WHILE DATE(dateFrom) <= DATE(dateTo) DO

        call first(dateFrom, @res);

// here I need another loop through all results of the first procedure to insert each one of them in the following line.

        insert into table_x (**some values which have been returned**);
                
        SET dateFrom = DATE_ADD(dateFrom, INTERVAL 1 month);
    END WHILE;
END

Upvotes: 0

Views: 1740

Answers (2)

Luuk
Luuk

Reputation: 14920

This might work, if all ** some values ** are replaced.

Minimal version of MySQL needed is 8.0 (or an equivalent MariaDB version).

The two variables @from and @to are set to let this script know from which data to which date is needed.

This code is untested, because OP wrote "I have two stored procedures in MySql", but he uses things like "** some values **", which make the two stored procedures invalid (sigh).

Final remark: It is, of course, possible to wrap this complete statement into a stored procedure. (If above corrections are made)

set @from = '2021-05-01';
SET @to   = '2021-06-13';

insert into table_x (** some values **) 
WITH recursive dates AS (
   SELECT @from as d
   UNION ALL
   SELECT date_add(d, INTERVAL 1 DAY) 
   FROM dates
   WHERE d<@to
   )
SELECT d, **some values which have been returned**
FROM dates
CROSS JOIN (
   SELECT 
      item.name,
      sum(amount) 
   FROM
      FOO
   INNER JOIN
      BAR ON id = id
   WHERE
      date(someDate) < date(d) 
group by something) sq

Upvotes: 0

nbk
nbk

Reputation: 49375

I don-' think that the loop and the second procedure ist really necessary

MySQL can't return table arrays or something of that kind, but you can use temporary tables

DELIMITER $$
CREATE PROCEDURE `first`(IN `start` date )
begin 
DROP TEMPORARY TABLE IF EXISTS myTable;
CREATE TEMPORARY TABLE myTABLE
SELECT 
    item.name,
    sum(amount) 

FROM
    FOO
        INNER JOIN
    BAR ON id = id
WHERE
        date(someDate) < date(start) 
group by something;
end$$
DELIMITER ;

Outer procdudre

DELIMITER $$
CREATE PROCEDURE `runner`(IN `_from` date, IN `_to` date)
BEGIN
    set dateTo = date(_to);

    set dateFrom = date(_from);

    WHILE DATE(dateFrom) <= DATE(dateTo) DO

        call first(dateFrom, @res);



        insert into table_x (SELECT * FROM myTable);
                
        SET dateFrom = DATE_ADD(dateFrom, INTERVAL 1 month);
    END WHILE;
END$$
DELIMITER ;

You ca make dynamic sql work with different variables

DELIMITER $$
CREATE PROCEDURE `first`(IN `_start` date , IN _group varchar(100))
begin 
DROP TEMPORARY TABLE IF EXISTS myTable;
SET @sql := CONCAT("
CREATE TEMPORARY TABLE myTABLE
SELECT 
    item.name,
    sum(amount) 

FROM
    FOO
        INNER JOIN
    BAR ON id = id
WHERE
        date(someDate) < date(",_gRoup,") 
group by",_goup,";");

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt; 
end$$
DELIMITER ;

Upvotes: 1

Related Questions