How can we create a table from stored procedure?

Question

CREATE DEFINER=`root`@`localhost`
PROCEDURE `create_tb_one`(In tableName varchar(255))
BEGIN

    CREATE table tableName(
        ID INT      NOT NULL,
        SNAME VARCHAR(100)  NOT NULL,
        SSTATE VARCHAR(100) NOT NULL,
        TIME_STAMP VARCHAR(45) NOT NULL,
        constraint FK_SENSOR
            foreign key (SNAME)
            references sensorInfo(SName)
            on delete set null
    );END

Now, when I call this procedure by passing a String, it is not creating a table and showing this following error code:

call create_tb_one(first) Error Code: 1054. Unknown column 'first' in 'field list' 0.00024 sec

Answer 1

I would do it with 2 steps: I would create a table template allowing to create as many tables as you want based on that table. Advantage is you only need to modify the table template, you don't need to touch the SP.

1. Create a table template;

   CREATE table tableName_template(
       ID INT NOT NULL,
       SNAME VARCHAR(100)  NOT NULL,
       SSTATE VARCHAR(100) NOT NULL,
       TIME_STAMP VARCHAR(45) NOT NULL,
       constraint FK_SENSOR
           foreign key (SNAME)
           references sensorInfo(SName)
           on delete set null
   );
   

2. Use this code

   DELIMITER $$
   CREATE DEFINER=`xxx`@`localhost`  (do not use root !! Create dedicated account) 
   PROCEDURE `create_tb_one` (In tableName varchar(255))
   BEGIN
   
       If not exists (select 1 FROM information_schema.tables
       WHERE table_schema=DATABASE()
       AND table_name=tableName ) 
       Then 
   
           SET @sql = CONCAT('CREATE TABLE ',tableName,' LIKE  tableName_template');
           PREPARE s FROM @sql;
           EXECUTE s;
           DEALLOCATE PREPARE s;
       END IF;
   
   
   END $$
   DELIMITER ;
   

Answer 2

You need Dynamic SQL

DELIMITER $$
CREATE DEFINER=`root`@`localhost`
PROCEDURE `create_tb_one`(In tableName varchar(255))
BEGIN
    SET @sql = CONCAT('CREATE TABLE IF NOT EXISTS ',tableName,' ');
    SET @sql = CONCAT(@sql,'(ID INT NOT NULL,SNAME VARCHAR(100) NOT NULL,');
    SET @sql = CONCAT(@sql,'SSTATE VARCHAR(100) NOT NULL,');
    SET @sql = CONCAT(@sql,'TIME_STAMP VARCHAR(45) NOT NULL,');
    SET @sql = CONCAT(@sql,'constraint FK_SENSOR ');
    SET @sql = CONCAT(@sql,'foreign key (SNAME) ');
    SET @sql = CONCAT(@sql,'references sensorInfo(SName) on delete set null)');
    PREPARE s FROM @sql;
    EXECUTE s;
    DEALLOCATE PREPARE s;
END $$
DELIMITER ;

or

DELIMITER $$
CREATE DEFINER=`root`@`localhost`
PROCEDURE `create_tb_one`(In tableName varchar(255))
BEGIN
    SELECT COUNT(1) INTO @table_exists
    FROM information_schema.tables
    WHERE table_schema=DATABASE()
    AND table_name=tableName;

    IF @table_exists = 0 THEN
        SET @sql = CONCAT('CREATE TABLE ',tableName,' ');
        SET @sql = CONCAT(@sql,'(ID INT NOT NULL,SNAME VARCHAR(100) NOT NULL,');
        SET @sql = CONCAT(@sql,'SSTATE VARCHAR(100) NOT NULL,');
        SET @sql = CONCAT(@sql,'TIME_STAMP VARCHAR(45) NOT NULL,');
        SET @sql = CONCAT(@sql,'constraint FK_SENSOR ');
        SET @sql = CONCAT(@sql,'foreign key (SNAME) ');
        SET @sql = CONCAT(@sql,'references sensorInfo(SName) on delete set null)');
        PREPARE s FROM @sql;
        EXECUTE s;
        DEALLOCATE PREPARE s;
    END IF;
END $$
DELIMITER ;

Give it a Try !!!