Create table variable in MySQL

Question

I need a table variable to store the particular rows from the table within the MySQL procedure. E.g. declare @tb table (id int,name varchar(200))

Is this possible? If yes how?

Answer 1

They don't exist in MySQL do they? Just use a temp table:

CREATE PROCEDURE my_proc () BEGIN 

CREATE TEMPORARY TABLE TempTable (myid int, myfield varchar(100)); 
INSERT INTO TmpTable SELECT tblid, tblfield FROM Table1; 

/* Do some more stuff .... */

From MySQL here

"You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed. This means that two different connections can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.)"

Answer 2

Perhaps a temporary table will do what you want.

CREATE TEMPORARY TABLE SalesSummary (
product_name VARCHAR(50) NOT NULL
, total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
, avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
, total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=MEMORY;

INSERT INTO SalesSummary
(product_name, total_sales, avg_unit_price, total_units_sold)
SELECT 
  p.name
  , SUM(oi.sales_amount)
  , AVG(oi.unit_price)
  , SUM(oi.quantity_sold)
FROM OrderItems oi
INNER JOIN Products p
    ON oi.product_id = p.product_id
GROUP BY p.name;

/* Just output the table */
SELECT * FROM SalesSummary;

/* OK, get the highest selling product from the table */
SELECT product_name AS "Top Seller"
FROM SalesSummary
ORDER BY total_sales DESC
LIMIT 1;

/* Explicitly destroy the table */
DROP TABLE SalesSummary;

From forge.mysql.com. See also the temporary tables piece of this article.