In MySQL: How to pass a table name as stored procedure and/or function argument?

Question

For instance, this does not work:

DELIMITER // CREATE PROCEDURE countRows(tbl_name VARCHAR(40))   BEGIN     SELECT COUNT(*) as ct FROM tbl_name;   END //  DELIMITER ; CALL countRows('my_table_name'); 

Produces:

ERROR 1146 (42S02): Table 'test.tbl_name' doesn't exist 

However, this works as expected:

SELECT COUNT(*) as ct FROM my_table_name; 

What syntax is required to use an argument as a table name in a select statement? Is this even possible?

Answer 1

Prepared statements are what you need.

CREATE  PROCEDURE `test1`(IN tab_name VARCHAR(40) ) BEGIN  SET @t1 =CONCAT('SELECT * FROM ',tab_name );  PREPARE stmt3 FROM @t1;  EXECUTE stmt3;  DEALLOCATE PREPARE stmt3; END $$ 

Answer 2

You can do it like this:

 DROP PROCEDURE IF EXISTS `getDataUsingSiteCode`;     DELIMITER $$     CREATE PROCEDURE `getDataUsingSiteCode`(           IN tab_name VARCHAR(40),            IN site_ VARCHAR(255)        )         BEGIN           SET @site_code = site_;           SET @sql_ =CONCAT('SELECT * FROM ',tab_name,' WHERE site=?');           PREPARE statement_ FROM @sql_;           EXECUTE statement_ using @site_code;         END$$     DELIMITER ;