Mobile Number Tracker
Trace Mobile Number Earn Money Online
© TechWelkin.com

Creating table with a name specified by a variable

Samyak Lalit | July 8, 2008 (Last update: November 9, 2014)

Samyak Lalit is an Indian author and disability rights activist. He is the principal author and founder of projects like TechWelkin, WeCapable, Viklangta, Kavita Kosh among many others.

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.

© TechWelkin.com

3 responses to “Creating table with a name specified by a variable”

  1. Juansta says:

    Dont forget to DEALLOCATE PREPARE each prepared statement

  2. Hanan says:

    That’s an extremely useful hint.
    Many thanks!

  3. Izabel Perez says:

    Hello,
    I am new using MYSQL and need help.I have a query that I use to create the tables. But every time have to change the table name.
    I have some standard fields that I use in all tables. How can use a stored procedure to create the table and pass the name of the table as parameter
    Any help is greatly appreciated.

Leave a Reply

Your email address will not be published. Required fields are marked *