CODEWITHSUNDEEP
mysqlsqlstored-procedures
Ciaran
Ciaran

Reputation: 169

Stored procedure - looping through results and store them in a temporary table

My issue I'm having is that Mysql workbench is reporting a SQL Syntax Error near 'DELARE tempOrder_id int;' with declare underlined in red although I cant seem to see an error, also haven’t been able to test this yet either so if you spot any other errors or more efficient ways to run this please say. it should be easy to under stand I'm grabbing the id from a query then running a new query with that id and storing it in a table so that I can return it

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE PROCEDURE `DLP_orders ` ()
BEGIN
    Create temporary table if not exists MyReturnTable
        (id int, order_id int, total_tickets int, product_id int, product_name varchar(255), PRIMARY KEY (id)) ENGINE=MEMORY;

    DECLARE tempOrder_id int;
    DECLARE order_Cursor;

    SELECT o.id FROM orders as o
        INNER JOIN orders_items as oi
            ON o.id = oi.order_id
        INNER JOIN contracts as c
            ON c.id = oi.contract_id
    WHERE (o.created BETWEEN '2013-01-01 00:00:00' AND '2013-02-02 23:59:59' OR o.updated BETWEEN '2013-01-01 00:00:00' AND '2013-02-02 23:59:59') AND (c.supplier_id = 95 || c.supplier_id = 255);

    OPEN order_Cursor;
    FETCH NEXT FROM order_Cursor INTO @tempOrder_id;

    WHILE @@FETCH_STATUS = 0
        BEGIN
            INSERT INTO MyReturnTable (order_id, total_tickets,  product_id, product_name)
                SELECT o.id, oi.total_tickets, p.id, p.name FROM orders as o
                    INNER JOIN orders_items as oi
                        ON o.id = oi.order_id
                    INNER JOIN products as p
                        ON p.id = oi.product_id
                    INNER JOIN contracts as c
                        ON c.id = oi.contract_id
                WHERE o.id = @tempOrder_id;

            FETCH NEXT FROM order_Cursor INTO @tempOrder_id;
        END;

    CLOSE order_Cursor;
    DEALLOCATE order_Cursor;

    SELECT * FROM MyReturnTable;
END$$

Upvotes: 0

Views: 3422

Answers (1)

Ciaran
Ciaran

Reputation: 169

This was my solution after a bit of tweaking

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE PROCEDURE `DLP_orders` ()
BEGIN
    DECLARE tempOrder_id int;
    DECLARE order_Cursor CURSOR FOR
    SELECT o.id FROM orders as o
        INNER JOIN orders_items as oi
            ON o.id = oi.order_id
        INNER JOIN contracts as c
            ON c.id = oi.contract_id
    WHERE (o.created BETWEEN '2013-01-01 00:00:00' AND '2013-02-02 23:59:59' OR o.updated BETWEEN '2013-01-01 00:00:00' AND '2013-02-02 23:59:59') AND (c.supplier_id = 95 || c.supplier_id = 255);

    OPEN order_Cursor;
    FETCH NEXT FROM order_Cursor INTO tempOrder_id;

    WHILE @@FETCH_STATUS = 0 DO
            INSERT INTO MyReturnTable (order_id, total_tickets,  product_id, product_name)
                SELECT o.id, oi.total_tickets, p.id, p.name FROM orders as o
                    INNER JOIN orders_items as oi
                        ON o.id = oi.order_id
                    INNER JOIN products as p
                        ON p.id = oi.product_id
                    INNER JOIN contracts as c
                        ON c.id = oi.contract_id
                WHERE o.id = @tempOrder_id;

            FETCH NEXT FROM order_Cursor INTO tempOrder_id;
    END WHILE;

    CLOSE order_Cursor;

    SELECT * FROM MyReturnTable;
END

Upvotes: 1

Related Questions