Mysql – How to we create a table from stored procedure

MySQLstored-procedures

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

Best Answer

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 ;
Related Question