CODEWITHSUNDEEP
mysqlstored-proceduresmysql-error-1064
irsis
irsis

Reputation: 1054

MySQL : How to Create table dynamically in stored procedure?

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

Upvotes: 2

Views: 5082

Answers (2)

antony thomas
antony thomas

Reputation: 27

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.

Upvotes: 0

irsis
irsis

Reputation: 1054

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))" );
                                                                                           ^

Upvotes: 2

Related Questions