CODEWITHSUNDEEP
mysqlstored-proceduresroutines
colonel_px
colonel_px

Reputation: 305

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?

Upvotes: 2

Views: 8641

Answers (4)

AlMounkez
AlMounkez

Reputation: 77

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;

Upvotes: 0

alanng
alanng

Reputation: 111

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.

Upvotes: 0

maxm
maxm

Reputation: 5371

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

Upvotes: -1

Devart
Devart

Reputation: 122032

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

CREATE TABLE Syntax

Upvotes: 1

Related Questions