0

I want to run multi-insert in MySQL stored procedure using prepared statements. Here is my query:

CREATE DEFINER=`user`@`%` PROCEDURE `insertMult`(IN insertStatement VARCHAR(999999))
BEGIN

SET @t1 =CONCAT("INSERT into sga.errorData(roundId, userId, errorType, description, createdOn)values(1,11,'custom','cusErr','2020-01-01') INSERT into sga.errorData(roundId, userId, errorType, description, createdOn)values(1,11,'custom','cusErr','2020-01-01')");

PREPARE stmt1 FROM @t1;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END

But when I run it, I got error;

Error : 1064 (42000): 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 'INSERT INTO sga.errorData(roundId, userId, errorType, description, createdOn)val' at line 1

it runs perfectly fine, when there's only one insert like this;

CREATE DEFINER=`user`@`%` PROCEDURE `insertMult`(IN insertStatement VARCHAR(999999))
BEGIN

SET @t1 =CONCAT("INSERT into sga.errorData(roundId, userId, errorType, description, createdOn)values(1,11,'custom','cusErr','2020-01-01')");

PREPARE stmt1 FROM @t1;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END

It also runs fine when I simply run multiple inserts instead of prepared statements. I don't know why is it not working with multiple inserts in Prepared statements. Any guesses?

Naila Akbar
  • 3,033
  • 4
  • 34
  • 76
  • Does this answer your question? [Execute multiple semi-colon separated query using mysql Prepared Statement](https://stackoverflow.com/questions/20371677/execute-multiple-semi-colon-separated-query-using-mysql-prepared-statement) – sticky bit May 12 '20 at 10:34
  • well it didn't.. but yes it gave me idea to solve my issue.. I don't know I should post that idea as answer here or delete my question.. – Naila Akbar May 12 '20 at 11:04

0 Answers0