mysqlmysql-error-1064

MySQL INSERT query not working in Stored Procedure


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 INTOtable1(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?


Solution

  • 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.