Execute Immediate in a Stored Procedure
I would like to execute 2 insert statements within and Execute Immediate command in the stored procedure. See example below. Insert 2 rows in the KJS_TEST table. The procedure gets created but when I execute the procedure, it fails and says the following. Only using one insert statement in the @STMT variable works fine. Any help would be greatly appreciated.
"#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INSERT INTO OCCIS_KJS_TEST VALUES ('5')' at line 1"
DELIMITER $$ CREATE PROCEDURE KJS_TESTPROC() BEGIN SET @STMT:= 'INSERT INTO KJS_TEST VALUES (''4'');INSERT INTO KJS_TEST VALUES (''5'');';
EXECUTE IMMEDIATE @STMT; END;
Answer
EXECUTE IMMEDIATE, whether in a stored procedure or not, can only handle a single SQL statement. It cannot handle two inserts as you have set @STMT to.
