Execute multiple semi-colon separated query using mysql Prepared Statement

by

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

More Related Contents:

  1. i have an error which i dont understand. can you help me? [closed]
  2. In SQL Server how to get the count of child if both parent and child are available [closed]
  3. Delete the entire content of all table in a Database [closed]
  4. MySQL select 10 random rows from 600K rows fast
  5. Get a list of dates between two dates
  6. Calculate a running total in MySQL
  7. Simulate lag function in MySQL
  8. What’s faster, SELECT DISTINCT or GROUP BY in MySQL?
  9. Get record counts for all tables in MySQL database
  10. How do I check if a column is empty or null in MySQL?
  11. Use MySQL spatial extensions to select points inside circle
  12. INSERT … ON DUPLICATE KEY (do nothing)
  13. What is this operator in MySQL?
  14. MySQL create stored procedure syntax with delimiter
  15. Mysql Convert Column to row (Pivot table )
  16. Merge two tables in one SQL query and make the date values unique
  17. How to find non-existing data from another Table by JOIN?
  18. MySQL/SQL: Update with correlated subquery from the updated table itself
  19. MySQL #1093 – You can’t specify target table ‘giveaways’ for update in FROM clause
  20. Greatest value of multiple columns with column name?
  21. Are SELECT type queries the only type that can be nested?
  22. Strings as Primary Keys in MYSQL Database [closed]
  23. MySQL : ERROR 1215 (HY000): Cannot add foreign key constraint
  24. Select data from “show tables” MySQL query
  25. What is dynamic SQL?
  26. SQL doesnt differentiate u and ü although collation is utf8mb4_unicode_ci
  27. MySQL – why not index every field?
  28. MySQL: Insert datetime into other datetime field
  29. Selecting distinct 2 columns combination in mysql
  30. Why in SQL NULL can’t match with NULL?

Leave a Comment