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?
