MySQL stored procedure parameter not working

Question

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 ;

Nick · Accepted Answer · 2019-02-05 05:12:05Z

2

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;

answered Feb 5, 2019 at 5:12

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL stored procedure parameter not working

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related