Mysql stored procedure does not accept table name as parameter

Question

I wrote a stored procedure. It works fine, except that the table name is an input parameter.

Let's see my proc in MySQL:

DELIMITER $$ USE `db_test`$$ DROP PROCEDURE IF EXISTS test_proc$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `test_proc`(IN serviceName VARCHAR(10),IN newsInfoTable VARCHAR(100)) BEGIN SELECT COUNT(*) FROM newsInfoTable WHERE newsServiceName=serviceName; END$$ DELIMITER ; 

Parameters of the stored procedure call:

 USE db_test; CALL test_proc('abc','tbl_test_news'); 

Here the service name parameter is working fine. But if I include the newsInfoTable variable as a table input parameter, then an error appears.

Table 'db_test.newsinfotable' does not exist

Why does this happen only for a table parameter? How can I extract from this error or

How to pass a table name to a stored procedure as a parameter?

Answer 1

It is not possible to optimize a SP with a dynamic table name, so many DBs, including MySQL, do not allow dynamic table names.

One way to use Dynamic SQL.

 CREATE DEFINER=`root`@`localhost` PROCEDURE `test_proc`(IN serviceName VARCHAR(10),IN newsInfoTable VARCHAR(100)) BEGIN SET @sql = CONCAT('SELECT COUNT(*) FROM ',newsInfoTable,' WHERE newsServiceName=?;'); PREPARE s1 from @sql; SET @paramA = serviceName; EXECUTE s1 USING @paramA; END$$ 

Answer 2

You can use EXECUTE IMMEDIATE to solve “less is more” (for me, less code = good)

 CREATE PROCEDURE test_proc(IN serviceName VARCHAR(10), IN newsInfoTable VARCHAR(100)) BEGIN EXECUTE IMMEDIATE CONCAT('SELECT COUNT(*) FROM ',newsInfoTable,' WHERE newsServiceName=''', serviceName, ''''); END 

Answer 3

that part of the request cannot be dynamic.

you might consider implementing as a string that executes dynamically at runtime

Answer 4

Although it may not be what you want, alternatively, it can be considered conditional, if you prepare the operator.

 DELIMITER $$ CREATE PROCEDURE select_count(IN table_name VARCHAR(20)) BEGIN IF table_name = 'xxx' THEN SELECT * FROM xxx; ELSEIF table_name = 'yyy' THEN ... ENDIF END$$