Search code examples
mysqlprepared-statementconcatenation

Mysql Prepare Statement error while executing

I am trying to create a table though prepare statement but it is giving me syntax error. Well if I try to execute the same statement individually then it works fine.

Here's my statement -

SET @Stmt1 = Concat('DROP TABLE IF EXISTS ',DB,'.`county`;\n'
'CREATE TABLE IF NOT EXISTS ',DB,'.`County`
(
  `CountyID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `CountyName` VARCHAR(45) NOT NULL,
  `CountyCode` VARCHAR(30) NOT NULL,
   PRIMARY KEY (`CountyID`)
)');

Prepare stmt2 From @stmt1;
Execute stmt2;

Please can anyone tell me what am I missing in this statement? It is giving me an error on this line: 

'CREATE TABLE IF NOT EXISTS ',DB,'.`County`
(
  `CountyID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
Solution

  • http://dev.mysql.com/doc/refman/en/prepare.html says:

    The text [of the preparable statement] must represent a single SQL statement, not multiple statements.

    So you'll have to execute your DROP TABLE statement first, and then prepare and execute the CREATE TABLE statement separately.