Execute multiple semi-colon separated query using mysql Prepared Statement

Question

I am trying to create a stored procedure in mysql which creates a new table on every request copies the content from another table and extracts the required data and finally drops the table. The stored procedure is quite large so I cant have EXECUTE after every query and thus I am trying to execute the query all together in a semicolon separated format. But on final execution I get Error Code: 1064. Is the approach I am trying possible, or is there a better approach.

SET tableName = (SELECT CONCAT("table",(UNIX_TIMESTAMP(NOW()))));

SET @tquery =CONCAT('CREATE TABLE `',tableName,'` (select pt.* from post_table pt join on user u on pt.user_id=u.id where pt.client="client",pt.group="group");');
SET @tquery = CONCAT(@tquery,' SELECT * FROM ',tableName,';');    
SET @tquery = CONCAT(@tquery,' DROP TABLE ',tableName,';');    
PREPARE stmt FROM @tquery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;  

Answer 1

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))
BEGIN
  SET @tquery = p_sql;
  PREPARE stmt FROM @tquery;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END 
/

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

Answer 2

I took the code from this answer and added a loop so can execute multiple queries passed to the stored procedure via single string parameter.

Guessing this might not have much by way of real-world utility, but just sharing for the heckuvit. I thought to want it when repeatedly wanting to drop then create tables in stored procedures and functions while learning mysql and wasting lots of time chasing my tail before stumbling on to the realization that you can't do multiple statements using PREPARE/EXECUTE.

# mysql_splitprepareexecutemulti.sql
# 
# combine codebits from these two pages: 
# 1 - https://stackoverflow.com/questions/20371677/execute-multiple-semi-colon-separated-query-using-mysql-prepared-statement
# 2 - https://sebhastian.com/mysql-split-string/
# 
# Not gonna bother validate the sql at all. May not even handle 
# empty string, no delimiter on end, etc.
# 
# Take query string with presumably 1 or more sql statements
#  - split based on parm string, default to semicolon
#  - loop through parm str sql, prepare each query then execute each.
# 
# 
##############################################
CREATE DEFINER=`root`@`localhost` PROCEDURE `mssp_execStrungQueries`(
  IN str text,
  IN delimiterChar CHAR(1) 
)
BEGIN 
  SET @inputString = str;

  WHILE LOCATE(delimiterChar,@inputString) > 1 DO
    SET @queryString = SUBSTRING_INDEX(@inputString,delimiterChar,1);
    PREPARE stmt FROM @queryString; EXECUTE stmt; DEALLOCATE PREPARE stmt;

  SET @inputString = REGEXP_REPLACE(@inputString, ( 
      SELECT LEFT(@inputString, LOCATE(delimiterChar, @inputString))),'',1,1); 
 select SLEEP(1); #slow down during dev in case conjur infinite loop 
  END WHILE;
END

##############################################
##############################################
### test calls to the SP:
call mssp_execStrungQueries(';',';');
select 'test just semi colon after call' as afterCallSP;

call mssp_execStrungQueries('','');
select 'test empty str after call' as afterCallSP;

SET @str = CONCAT('select \'ONEthingAAA\' as oneCol');
call mssp_execStrungQueries(@str,';');
select 'Test 1 query NO SEMICOLON test after call.' as afterCallSP;

SET @str = CONCAT('select \'ONEthingAAA\' as oneCol;');
call mssp_execStrungQueries(@str,';');
select 'Test 1 query WITH semicolon test after call.' as afterCallSP;

SET @str = CONCAT('select \'ONEthingAAA\' as oneCol;' 
                 ,'select \'TWOthingBBB\' as twoCol;'
                 ,'select \'TREEtingCCCC\' as threeCol;');
call mssp_execStrungQueries(@str,';');
select 'Test 3 statements after call' as afterCallSP;

SET @str = CONCAT('select \'ONEthingAAA\' as oneCol;' 
                 ,'select \'TWOthingBBB\' as twoCol;'
                 ,'select \'TREEtingCCCC\' as threeCol');
call mssp_execStrungQueries(@str,';');
select 'Test 3 stmts NO SEMI on last' as afterCallSP;

SET @str = CONCAT('select \'ONEthingAAA\' as oneCol;' 
                 ,'select \'TWOthingBBB\' as twoCol '
                 ,'select \'TREEtingCCCC\' as threeCol;');
call mssp_execStrungQueries(@str,';');
select 'Test 3 stmts NO SEMI on 2nd' as afterCallSP;