This has nothing to do with temporary tables specifically. You are attempting to use a variable and have it's value be used as the identifier for that table. That is, you are attempting to use dynamic SQL from inside a procedure.
Lucky for you, this is supported as of MySQL 5.0.13!
delimiter go
create procedure createtable(name varchar(64))
begin
set @stmt := concat('create temporary table ',name,'(id int)');
prepare stmt from @stmt;
execute stmt;
deallocate prepare stmt;
end;
go
drop table if exists mytable
go
call createtable ('mytable')
go
insert into mytable (id) values (1)
go
select * from mytable
go
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
Note that you reallly must use 'user variables' (the @-prefixed variable) or string literals in the PREPARE statement. You can't use normal stored procedure variables or parameters (yet?).
see:
http://dev.mysql.com/doc/mysql/en/sqlps.html
for the PREPARE syntax
Edited 1 time(s). Last edit at 10/07/2005 04:32AM by Roland Bouman.