User-defined Table Variables in MySQL 5.5?

I've recently moved 开发者_如何转开发from MSSQL to MySQL.

I would like to use a table variable (or equivalent) inside a MySQL 5.5 stored routine, to populate a dataset for an online report.

In MS SQL, I would do it this way

...
...
DECLARE @tblName TABLE
WHILE <condition>
BEGIN
    Insert Row based on iteration value
END
...
...

From what I understand, I can't declare table variables in MySQL (correct me if I'm wrong) How do I implement the above logic in a MySQL stored procedure?

You could create a table or temporary table and populate it with data you need.

CREATE TABLE Syntax

You understand that limitation correctly. The MySQL user manual clearly states that user-defined variables cannot refer to a table: http://dev.mysql.com/doc/refman/5.5/en/user-variables.html

User variables are intended to provide data values. They cannot be used directly in an SQL statement as an identifier or as part of an identifier, such as in contexts where a table or database name is expected, or as a reserved word such as SELECT.

create temporary table tmp ( id int unsigned not null, name varchar(32) not null ) engine=memory; -- change engine type if required e.g myisam/innodb

insert into tmp (id, name) select id, name from foo... ;

-- do more work...

select * from tmp order by id;

drop temporary table if exists tmp;

I think this covers it. Also, this may be helpful.