title: Stored procedure - looping through results and store them in a temporary table
body: <p>My issue I'm having is that Mysql workbench is reporting a SQL Syntax Error near '<code>DELARE tempOrder_id int;</code>' 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</p>
  
  <pre><code>-- --------------------------------------------------------------------------------
  -- 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$$
  </code></pre>