Stored procedure for dynamic create statement where number of columns keep changing

Question

I need to create a stored procedure to create a dynamic 'create statement' that will generate a new create statement everytime it runs.
I have table_A that has one column and that column has the list of column names that need to be in the create statement.
Example:

     Table_A:  columns   
      abcd   
      hijk   
      defg    

My create statement should look like:

    create table table_B (
    abcd varchar(255),
    hijk  varchar(255),
    defg varchar(255)
    );

After a few days the number of columns in Table_A can change/increase/decrease like:

  Table_A: columns         
        abcd
        pqrs
        defg
        ghij

My create statement should look like:

    create table table_B ( 
    abcd varchar(255),
    pqrs  varchar(255),
    defg varchar(255),
    ghij varchar (255)
    );

I need to write a stored procedure with cursors involved.

I started with something like:

    Delimiter $$
    DROP PROCEDURE IF EXISTS sp_test2 $$
    CREATE PROCEDURE sp_test2()
    BEGIN
    DECLARE DONE INT DEFAULT 0;
    DECLARE col1 varchar(255);
    DECLARE curA CURSOR FOR     select col AS column_name from Table_A;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = 1;
    OPEN curA;
    DROP TABLE IF EXISTS Table_B;
    while done = 0 do  
    fetch next from CurA into col;
    if done = 0 then 
    set @SQL_TXT = concat('CREATE TABLE Table_B (',col1,' varchar(255))');
    -- select @SQL_TXT
    PREPARE stmt_name FROM @SQL_TXT;
    EXECUTE stmt_name;
    DEALLOCATE PREPARE stmt_name;
    end if;
    end while;
    close curA;
    end

    call sp_test2()    

This works fine when I have just one row in a table(which is just one column in create table). How do I do it when I have multiple columns.

Answer 1

In case it was not clear from my original comment, creating tables like this is almost never a good idea.

But if you must, here are some ways....

Try replacing/rearranging this part (from the original question/problem):

DROP TABLE IF EXISTS Table_B;    
while done = 0 do  
fetch next from CurA into col;
if done = 0 then 
set @SQL_TXT = concat('CREATE TABLE Table_B (',col1,' varchar(255))');
-- select @SQL_TXT
PREPARE stmt_name FROM @SQL_TXT;
EXECUTE stmt_name;
DEALLOCATE PREPARE stmt_name;
end if;
end while;
close curA;

with/like something like this:

DROP TABLE IF EXISTS Table_B;
SET @SQL_TXT = '';
while done = 0 do  
    fetch next from CurA into col;
    if done = 0 then 
        set @SQL_TXT = concat(@SQL_TXT, ', `', col1, '` varchar(255)');
    end if;
end while;
close curA;
SET @SQL_TXT = CONCAT('CREATE TABLE Table_B (', SUBSTRING(@SQL_TXT, 2), ')');
PREPARE stmt_name FROM @SQL_TXT;
EXECUTE stmt_name;
DEALLOCATE PREPARE stmt_name;

The loop builds your field list, the SUBSTRING() removes he leading ',', and the final concat wraps the field list with the actual CREATE.

---

Or you can just build the query like this:

SET @SQL_TXT 
   = SELECT CONCAT('CREATE TABLE Table_B ('
                   , GROUP_CONCAT(CONCAT('`', col, '` VARCHAR(255)') SEPARATOR ',')
                   , ');' AS theQuery
     FROM Table_A
;

---

Also not in both cases I delimited the field name with the ` character; field names like "123" cannot be used without being delimited.