CODEWITHSUNDEEP
c#mysqlstored-proceduresparameters
royy
royy

Reputation: 35

MySQL stored procedure parameter not working

I am new to MySQL and I am trying to port some stored procedures from SQL Server. I created a basic stored procedure with parameter passing in MySQL.

The stored procedure simply takes a parameter and executes a 'drop if exist' based on the parameter supplied.

When running in MySQL results in an error below.

Seems that somehow, the parameter passed 'mytablename' into the stored procedure is executed as part of the statement instead of a parameter.

Any advice?

SQL statement:

call mydb.spDroptableifexist('customerdata');

Results:

0 row(s) affected, 1 warning(s): 1051 Unknown table 'mydb.mytablename'

This is the stored procedure:

USE `mydb`;
DROP procedure IF EXISTS `spDroptableifexist`;

DELIMITER $$
USE `mydb`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `spDroptableifexist`(IN mytablename VARCHAR(255))
BEGIN
    drop table if exists mytablename;
END$$

DELIMITER ;

Upvotes: 1

Views: 1704

Answers (1)

Nick
Nick

Reputation: 147286

From the manual:

User variables are intended to provide data values. They cannot be used directly in an SQL statement as an identifier or as part of an identifier, such as in contexts where a table or database name is expected

To use a variable as a table name, you need to prepare a statement and execute it:

SET @sql = CONCAT('DROP TABLE IF EXISTS ', mytablename);
PREPARE stmt FROM @sql;
EXECUTE stmt;

Upvotes: 2

Related Questions