I have a simple stored proc as following.
DELIMITER $$CREATE DEFINER=`dbadmin`@`%` PROCEDURE `Test_TruncateTable`(IN `spTableName` TEXT) MODIFIES SQL DATA COMMENT 'Truncates staging table.'BEGIN
TRUNCATE TABLE spTableName;
END$$DELIMITER ;
The procedure won't get executed, it throw's following error.
#1.
#2.
I tried changing the IN parameter to VARCHAR etc. but no luck. In past I have wrote more complicated procedures having multiple parameters however never run into the above mentioned error.
Discussion posts and replies are publicly visible
Hi, check the below code this error happens because the stored procedure is running the DDL statement.
DELIMITER $$ CREATE DEFINER=`dbadmin`@`%` PROCEDURE `Test_TruncateTable`(IN `spTableName` TEXT) MODIFIES SQL DATA COMMENT 'Truncates staging table.' BEGIN EXECUTE IMMEDIATE CONCAT('TRUNCATE TABLE ', spTableName, ';'); END$$ DELIMITER ;
Thanks. The use of Execute() function did the job.