CODEWITHSUNDEEP
mysql
Shahab Haidar
Shahab Haidar

Reputation: 641

can't create a table in mysql with dynamic sql

This is my code in MySQL.

USE database;
DROP procedure IF EXISTS CreateTable;
DELIMITER $$
USE ims_data$$
CREATE PROCEDURE CreateTable ()
    BEGIN
Set @SqlQuery = Concat('DROP TABLE IF EXISTS mytemptable;');
Set @SqlQuery = Concat(@SqlQuery,'\r\n','create table mytemptable');
Set @SqlQuery = Concat(@SqlQuery,'\r\n','(');
Set @SqlQuery = Concat(@SqlQuery,'\r\n','Column1 int,');
Set @SqlQuery = Concat(@SqlQuery,'\r\n','Column2 varchar(500)');
Set @SqlQuery = Concat(@SqlQuery,'\r\n',');');
Set @SqlQuery = Concat(@SqlQuery,'\r\n','Select * from mytemptable;');
#Select @SqlQuery;
PREPARE Statement From @SqlQuery;
EXECUTE Statement;  
DEALLOCATE PREPARE Statement;
END$$

DELIMITER ;


call GetUploadInformation();

I am trying to create a table but it is giving me an error.

Error Code: 1064. 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 'create table mytemptable (Stockist_Code int,Status varchar(500) ); Sele' at line 2

This is the output of query.

    DROP TABLE IF EXISTS mytemptable;
create table mytemptable
(
Column1 int,
Column2 varchar(500)
);
Select * from mytemptable;

Which is working fine when executing this code withoug calling the procedure.

Upvotes: 0

Views: 41

Answers (1)

Eric J.
Eric J.

Reputation: 150148

PREPARE/EXECUTE can only process one statement at a time. You're trying to execute two with the ;.

The error message gives you a clue in that it ran the two statements together.

You'll have to run them as separate statements.

Upvotes: 2

Related Questions