can't create a table in mysql with dynamic sql

Question

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.

Eric J. · Accepted Answer · 2019-07-09 07:27:29Z

2

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.

answered Jul 9, 2019 at 7:27

Eric J.

151k65 gold badges353 silver badges563 bronze badges

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

2 Comments

Shahab Haidar Over a year ago

I commented out each line and made only one statement then too it is giving the same error

Eric J. Over a year ago

If you comment out the line including the SELECT you won't have exactly the same error because at a minimum SELE won't be part of it. Try commenting out that line and ALSO removing the semicolon from the previous line, e.g. ');' becomes ')'

Collectives™ on Stack Overflow

can't create a table in mysql with dynamic sql

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related