Execute multiple semi-colon separated query using mysql Prepared Statement

No, it is not possible. PREPARE / EXECUTE stmt can execute only one query at a time, many statements cannot be combined.
See documentation: http://dev.mysql.com/doc/refman/5.0/en/prepare.html

… a user variable that contains the text of the SQL statement. The text must represent a single statement, not multiple statements.

Anyway, to simplify your code I would create a simple procedure:

CREATE PROCEDURE exec_qry( p_sql varchar(100))
  SET @tquery = p_sql;
  PREPARE stmt FROM @tquery;
  EXECUTE stmt;

and I would call this procedure in the main procedure, in this way:

CALL exec_qry( 'CREATE TABLE t2 AS SELECT * FROM test');
CALL exec_qry( 'SELECT * FROM t2');
CALL exec_qry( 'SELECT count(*) FROM t2');
CALL exec_qry( 'SELECT avg(x) FROM t2');
CALL exec_qry( 'DROP TABLE t2');

Take a look at a demo: http://www.sqlfiddle.com/#!2/6649a/6

Leave a Comment