Creating table with a name specified by a variable

Today I needed to write a stored mysql procedure which could take a parameter and create a new table with the name same as the value of this parameter. Everything was straightforward except that the name of the newly created table was turning out to be the name of the parameter instead of the value of parameter.

It was an intriguing problem and took quite a bit of research on Internet before I could find a solution. The solution is to use the PREPARE statement. Here is the code that worked (I am truncating the CREATE TABLE and INSERT INTO statements to make it easier to read):

DELIMITER $$

DROP PROCEDURE IF EXISTS `tdb_new`.SP_createTempRankTable$$

CREATE DEFINER=`root`@`localhost` PROCEDURE SP_createTempRankTable(IN tableName VARCHAR(200))

BEGIN

SET @dropTab=CONCAT("DROP TABLE IF EXISTS ", tableName);

SET @createTab=CONCAT("CREATE TABLE ", tableName, " (tmp_template_id varchar(10))");

SET @insertTab=CONCAT("INSERT INTO ", tableName, " (tmp_template_id) SELECT tmp_template_id FROM template");

PREPARE stmtDrop FROM @dropTab;
EXECUTE stmtDrop;

PREPARE stmtCreate FROM @createTab;
EXECUTE stmtCreate;

PREPARE stmtInsert FROM @insertTab;
EXECUTE stmtInsert;

END$$

DELIMITER ;

Passing the table name as variable to CREATE TABLE, INSERT INTO and DROP TABLE (and probably a lot of other) statements will not work. Prepared statements are the way out.

Hope this would be of help to someone! Please feel free to ask me if you have any questions. I will be happy to try and help you. Thank you for using TechWelkin.