MySQL : How to Create table dynamically in stored procedure?

Question

I want to drop and create a table with name passed in the stored procedure argument. I've written following stored procedure.

CREATE DATABASE db1;
USE db1;
DROP PROCEDURE IF EXISTS drop_create;
DELIMITER $$  
CREATE PROCEDURE drop_create(IN tbname VARCHAR(15))
BEGIN

SET @droptable = CONCAT ("DROP TABLE IF EXISTS ", tbname);
SET @createtable = CONCAT("CREATE TABLE ", tbname, " (c0 INT PRIMARY KEY,  c1 VARCHAR(16000)" );

PREPARE deletetb FROM @droptable;
PREPARE createtb FROM @createtable ;

EXECUTE deletetb ; 
EXECUTE createtb; 

DEALLOCATE PREPARE createtb ;
DEALLOCATE PREPARE deletetb ;

END $$
DELIMITER ;

When I execute it, I get following error.

Call db1.drop_create('abd');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

Apparently, I am not able to find the syntax error in SP above. What am I doing wrong? Problem must be in CREATE TABLE related statements because when I comment line#8, 10, 12 & 13 then SP works fine. I am using Mysql 5.6

Arulkumar · Accepted Answer · 2016-05-05 11:46:14Z

2

Oops! a closing brace was missing in the following statement:

SET @createtable = CONCAT("CREATE TABLE ", tbname, " (c0 INT PRIMARY KEY, c1 VARCHAR(16000)" );

It should be:

SET @createtable = CONCAT("CREATE TABLE ", tbname, " (c0 INT PRIMARY KEY, c1 VARCHAR(16000))" );
                                                                                           ^

edited May 5, 2016 at 11:46

Arulkumar

13.3k14 gold badges51 silver badges76 bronze badges

answered May 5, 2016 at 11:35

irsis

1,0851 gold badge15 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

antony thomas · Accepted Answer · 2016-12-09 05:11:59Z

0

Your code has many errors .it is being corrected as follows. VARCHAR cannot asume 1600.It can be arround 550.

DELIMITER $$  
CREATE DATABASE db1;
USE db1;
DROP PROCEDURE IF EXISTS drop_create;

CREATE PROCEDURE drop_create(IN tbname VARCHAR(15))
BEGIN

SET @droptable = CONCAT ('DROP TABLE IF EXISTS  ', tbname);
PREPARE deletetb FROM @droptable;
EXECUTE deletetb ;
DEALLOCATE PREPARE deletetb ;

SET @createtable = CONCAT('CREATE TABLE ', tbname, ' (c0 INT  NOT NULL AUTO_INCREMENT, c1 VARCHAR(550),PRIMARY KEY(c0))ENGINE=InnoDB DEFAULT CHARSET=utf8');
PREPARE createtb FROM @createtable ; 
EXECUTE createtb;
DEALLOCATE PREPARE createtb ;

END $$
DELIMITER ;

NOW try.The code should works well.

answered Dec 9, 2016 at 5:11

antony thomas

272 bronze badges

3 Comments

Bill Karwin Over a year ago

VARCHAR can be up to 65535 bytes in MySQL. See The CHAR and VARCHAR types.

antony thomas Over a year ago

Thank you Mr.Bill Kanwin for being isolated a useful addition.

antony thomas Over a year ago

Thank you Mr.Bill Karwin for being isolated a useful addition.

Collectives™ on Stack Overflow

MySQL : How to Create table dynamically in stored procedure?

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related