How To Create Dynamic Table Using Mysql Stored Procedure..

Hi Guys,
              Here I am writing the snippet code of mysql stored procedure which shows you how to create dynamic table using mysql stored procedure & dynamic SQL prepared statement and cursor. Many of the database developer needs this types of technique in there database developments , so I am writing the snippet for those  database developer who are facing the problems for developing the same.
Go through the snippet code.


DROP PROCEDURE IF EXISTS dynamictable;

DELIMITER //


CREATE PROCEDURE dynamictable()

BEGIN

DECLARE done INT DEFAULT 0;
DECLARE tnm varchar(20);


# Selecting the table Name from table in which you have stored one by one using Cursor.
DECLARE curname cursor FOR SELECT distinct(tname)  FROM table_name;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

open curname ;
FETCH curname INTO tnm;
REPEAT

# Dropping table dynamically if exist with same name which you are creating.
SET @stm = CONCAT('DROP TABLE IF EXISTS ', tnm);
PREPARE stmt FROM @stm;
EXECUTE stmt;


#  Creating table and selecting the table structure from table tbl_name.
SET @stm2 = CONCAT('CREATE TABLE  ', tnm, ' as SELECT * FROM tbl_name where 1=2');
PREPARE stmt2 FROM @stm2;
EXECUTE stmt2;

FETCH curname INTO tnm;

UNTIL done=1 END REPEAT;
set done=0;

END //

DELIMITER ;

Call dynamictable();




Creating  Table Dynamically in Runtime.


DROP PROCEDURE IF EXISTS dynamictable;

DELIMITER //

CREATE PROCEDURE dynamictable(IN tname VARCHAR(20))

BEGIN

SET @stm = CONCAT('DROP TABLE IF EXISTS ', tname);
PREPARE stmt FROM @stm;
EXECUTE stmt;

SET @stm2 = CONCAT('CREATE TABLE ', tname, ' as SELECT * from tablename where 1=2');
PREPARE stmt2 FROM @stm2;
EXECUTE stmt2;

END //

DELIMITER ;




Hope this will work for you

Gud Luck.