i need to create table with a variable name.
Heres my code, i dont know why it not work.
BEGIN
SET @tablename = tablename;
SET @sql_text = concat('CREATE TABLE ',@tablename,' (ID INT(11) NOT NULL, team0 DOUBLE NOT NULL, team1 DOUBLE NOT NULL)');
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
And here is the error:
Procedure execution failed
1054 - Unknown column 'TestTableName' in 'field list'
Wrap tablename with ' to indicate that it is string literal and not identifier.
BEGIN
SET @tablename = 'tablename';
SET @sql_text = concat('CREATE TABLE ',@tablename,' (ID INT(11) NOT NULL, team0 DOUBLE NOT NULL, team1 DOUBLE NOT NULL)');
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
And please read CREATE TABLE @tbl because creating tables at runtime could indicate poor design.
BEGIN
SET @tablename = 'tablename';
SET @sql_text = concat('CREATE TABLE ',@tablename,' (ID INT(11) NOT NULL, team0 DOUBLE NOT NULL, team1 DOUBLE NOT NULL)');
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
Obviously the problem is the table name itself and the lack of quotes (as others have noted). When writing this type of code, you might consider using replace() rather than concat(). This allows you to do:
BEGIN
SET @tablename = 'tablename';
SET @sql_text = '
CREATE TABLE @tablename (
ID INT(11) NOT NULL,
team0 DOUBLE NOT NULL,
team1 DOUBLE NOT NULL
)';
REPLACE(@sql_text, '@tablename', @tablename);
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
I find that this approach is much easier to debug and maintain.
SET @sql_text = REPLACE(@sql_text, '@tablename', @tablename);? Without SET the original variable will be unaffected + you need to indicate that it is variable with @ (typo probably).