0

I could not find any tutorials on MySQL procedures that can execute multiple queries using prepared statement. Whenever i try to run such statement, i end up with errors exactly after when the first query is completed and next query is started (This will be more clear if the error provided below is seen). However, here: How to execute multiple mysql queries together in PHP?, some clues has been provided on how to achieve the same in PHP (using mysql dbms). But i would like to achieve this without connecting it to any other programming or scripting languages. Please help me, if this is possible.

Thanks in advance. The snippets of code is provided below with the error,

use student;

delimiter //
create procedure test() 
begin
set @dbname = 'test_db';
set @tablesDeletion = concat('drop table if exists ',@dbname,'.table1;', 
                             ' drop table if exists ',@dbname,'.table2;',
                             ' drop table if exists ',@dbname,'.table3;',
                             ' drop table if exists ',@dbname,'.table4;',
                             ' drop table if exists ',@dbname,'.table5;',
                             ' drop table if exists ',@dbname,'.table6;');

prepare dropTmpTables from @tablesDeletion;
execute dropTmpTables;
deallocate prepare dropTmpTables;
end//
delimiter ;
call test();

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 'drop table if exists test_db.table1; drop table if exists test_db.table2; dr' at line 1

Community
  • 1
  • 1
Syrus
  • 25
  • 6

2 Answers2

0

Within a stored procedure you can execute multiple statements however this isn't really the way to do it. Instead you need to prepare each DROP statement individually and execute them one by one, i.e. 

set @tablesDeletion = concat('drop table if exists ',@dbname,'.table1');
prepare dropTmpTables from @tablesDeletion;
execute dropTmpTables;
deallocate prepare dropTmpTables;

The real question is why you are doing this with a static database name. You could instead create a stored procedure to do the following to do each individual DROP

DELIMITER //
CREATE PROCEDURE test(dbName,tableName) 
BEGIN
    SET @tablesDeletion = CONCAT('DROP TABLE IF EXISTS ',dbName,'.',tableName);

    PREPARE dropTmpTables FROM @tablesDeletion;
    EXECUTE dropTmpTables;
    DEALLOCATE PREPARE dropTmpTables;
END//
DELIMITER ;
Simon at The Access Group
  • 6,932
  • 22
  • 45
  • Thank you Simon for your prompt reply. I could have done it dynamically too as you suggested. But again i have to call the procedure 7 times to drop these seven possible tables. I was hoping if i could do anything as in exec() function in MSSQL. Thank you once again. – Syrus Jul 08 '14 at 09:55
  • You can make a second procedure that calls this procedure, and then you just call that second procedure once. Alternatively put a loop of some kind into the first procedure - there's various ways you could make this work. I'm not aware of a way to do as you specified, but that's not to say there isn't one - I just may not be aware of it. Feels like unnecessary complication. – Simon at The Access Group Jul 08 '14 at 13:16
  • Hi Simon, actually i am working on MSSQL to MySQL conversion where several queries of various natures (e.g. select, insert, set and cursor and so on) exists in exec() function of MSSQL. Therefore, i was attempting to achieve this in MySQL. Thanks, – Syrus Jul 09 '14 at 07:57
  • Ok well prepared statements seem to be what you are after then. According to the documentation (http://dev.mysql.com/doc/refman/5.6/en/prepare.html) it states explicitly `The text must represent a single statement, not multiple statements` and so I am afraid that the above is your way forward for this. – Simon at The Access Group Jul 09 '14 at 10:09
0

I have managed to solve my problem as the way mentioned below and eventually i was able to convert thousands of queries from MsSQL to MySQL. Thank you Simon for your suggestions.

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `executeQuery`(IN sqlCommand text)
begin
  SET @tquery = sqlCommand;
  PREPARE stmt FROM @tquery;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END

And by calling this procedure with the required query as a parameter i am able to get the results as required. For example,

 CALL executeQuery (concat('drop table if exists ',dbname,'.t1'));

and so on.
Thanks,

Syrus
  • 25
  • 6