Working with variables in stored proc

Question

I want to create a meta table on the fly using a stored pdocedure and passing a table prefix as "in" parameter and then build the final table name somehow.

My code looks like this:

SET @table_ = CONCAT(tablename, '_meta');
CREATE TABLE @table_ (name varchar(50), value varchar(50)) ENGINE=MyISAM;

Where tablename is the in parameter (as varchar(50) utf-8)

But I always face this error:

Syntax error near 'CREATE TABLE @table_ (name varchar(50), value varchar(50)) ENGINE=MyISAM'

Am I missing something? Anyone has a hint?

Answer 1

You can use prepare statement for this and also need a delimiter for the procedure something as

delimiter //

create procedure table_create (in tablename varchar(100))
begin
 set @table_ = concat(tablename, '_meta');
 set @qry = concat("create table ",@table_,"(name varchar(50), value varchar(50)) ENGINE=MyISAM;");
 prepare stmt from @qry;
 execute stmt;    
end;//

delimiter ;

Here is a test case in mysql

mysql> delimiter //
mysql> 
mysql> create procedure table_create (in tablename varchar(100))
    -> begin
    ->  set @table_ = concat(tablename, '_meta');
    ->  set @qry = concat("create table ",@table_,"(name varchar(50), value varchar(50)) ENGINE=MyISAM;");
    ->  prepare stmt from @qry;
    ->  execute stmt;    
    -> end;//
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> delimiter ;
mysql> call table_create('test');
Query OK, 0 rows affected (0.08 sec)

mysql> describe test_meta ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(50) | YES  |     | NULL    |       |
| value | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)