0
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

  • You cannot parametrise a table name like that – therefore, you will certainly need dynamic SQL. Question is, though, whether you really need to use a stored procedure to create tables. What's wrong with creating them manually beforehand? Or why can't the calling application build an ad-hoc CREATE TABLE statement and pass it to MySQL for execution? – Andriy M Sep 1 '15 at 13:34
  • The answers below tell you how to create the table. However, regarding the error you are getting, it is because you didn't enclose the name in quotes. i.e. try create_tb_one('first'); – Jehad Keriaki Sep 1 '15 at 20:51
2

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 ;
    
1

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 !!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.