I've been stuck into a problem for several hours now. I'm building a stored procedure in MYSQL with lots of Inserts in new physical tables. The following is just a sample that I'm unable to get running in my MySQL server.
DROP PROCEDURE IF EXISTS `someProc`$$
CREATE DEFINER=`test`@`%` PROCEDURE `someProc`()
BEGIN
DROP TABLE IF EXISTS table1;
SET @SQL1 = ' CREATE TABLE `table1` (`last_updated` INT);
INSERT INTO `table1` (`last_updated`)
VALUES (1); ';
-- select @SQL1;
PREPARE stmt2 FROM @SQL1;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;
END$$
DELIMITER ;
Upon invoking the stored proc, I get a MySQL error code 1064 saying that a error exists around 'INSERT INTO
table1(
last_updated)
.
But I can run the String contents assigned to the @SQL1 variable as a valid MySQL query and get the table created as well as the value inserted into it too. I've googled for the error code and as suggested in the blogs/documentation pages, I don't think I'm using a reserved keyword here nor I'm inserting any incompatible datatypes. It's plain simple SQL that works outside of the stored procedure setting but fails miserably inside a proc. Can anyone shed some light?
prepare
prepares one statement. This is clear in the documentation:
The text must represent a single statement, not multiple statements.
You need to split these two operations into two different statements.