mysql 动态传入表名 存储过程_MySQL存储过程不使用表名作为参数

I've written a stored procedure. It's working fine except taking the table name as input parameter.

Let 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 ;

Stored procedure calling parameters:

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 table input parameter then a error shows.

Table 'db_test.newsinfotable' doesn't exist

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

How I pass a table name into a stored procedure as a parameter?

解决方案

An SP cannot be optimized with a dynamic table name, so many DBs, MySQL included, don't allow table names to be specified dynamically.

One way around this is 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$$