Mysql stored procedure don't take table name as parameter

Question

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?

Jeff Parker · Accepted Answer · 2011-07-07T11:32:24.473

10

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$$

edited Jul 07 '11 at 11:32

answered Jul 07 '11 at 11:26

Jeff Parker

7,367
1
22
25

1

+1, be careful about SQL-injection though. You may want to test the tablename against a list of preapproved tables, lest the tablename is manipulated by a user to be `atable where (1=1) union all select username from mysql.user; '-- ` or something like that. I would make a table called `approvedtables` and put a test in like `select id into approvedid from approvedtables where tablename like newsinfotable limit 1` only execute the dynamic query if approved is not null. – Johan Jul 07 '11 at 12:08
I'd even go so far as saying one SP per table ... then you have not only injection protection surrounding the table name, but also better performance, even if only subtly. Still, mine is not to reason why, mine is but to do and post, get flamed a bit, feel hurt, make a smoothie so the pain goes away, live a happy life, then die in a swimming pool filled with cheesecake. – Jeff Parker Jul 07 '11 at 13:18
hello,from your above example i wrote a store proc.But it's giving error: Error Code : 1327 Undeclared variable: Third.Please see my proc below: DROP PROCEDURE IF EXISTS `test_proc`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `test_proc`(IN newsInfoTable VARCHAR(100)) BEGIN SET @sql_stam = CONCAT('SELECT news INTO ', @news,' FROM ',newsInfoTable,' WHERE ',CURDATE(),'=?;'); PREPARE s1 FROM @sql_stam; SET @where_param = DATE_FORMAT(date_time,'%Y-%m-%d'); EXECUTE s1 USING @where_param; SELECT @news; END$$ DELIMITER ; – riad Jul 07 '11 at 14:14
dear @jeff Parker: can you pls help me to solve the error.As per your direction i wrote a SP.It's execute properly but getting error when i call the SP.So,it's my kind request to you ,if you pls see my new question on http://stackoverflow.com/questions/6612530/error-when-send-table-as-parameter-in-mysql-store-procedure – riad Jul 07 '11 at 15:01
@riad It seems someone beat me to it ... but let me know if their solution doesn't work :) – Jeff Parker Jul 08 '11 at 08:24

score 6 · Answer 2 · answered Jul 07 '11 at 12:32

You can use EXECUTE IMMEDIATE for a "less is more" solution (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

score -1 · Answer 3 · answered Apr 01 '14 at 08:10

Although may not be what you want, alternatively, can consider to use conditionally if and prepare the statement.

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$$

score -1 · Answer 4 · answered Jul 07 '11 at 11:26

-1

that part of a query cannot be dynamic.

you may consider implementing as a string that is executed dynamically at runtime

answered Jul 07 '11 at 11:26

Randy

16,480
1
37
55

Mysql stored procedure don't take table name as parameter

4 Answers4

Linked

Related