MySQL stored procedure does not recognize parameter while executing.

Question

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.

ujjwalr0002 · Answer

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 ;

Sign up

Login with SSO

Login to the community

Login with SSO