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?
Best Answer
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.