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

sql mysql stored-procedures mysql-error-1146
randomx picture randomx · Jun 4, 2010 · Viewed 33.4k times · Source

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

a1ex07 picture a1ex07 · Jun 4, 2010

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

Related questions

MySQL Pass table name to cursor select

I want the procedure to take parameter answertable and partid in the select statement, but when i call it it doesn't replace the parameter answertable with the value the call call updateTotalScores('quiz_participation', 'quiz_answer', 1) returns the error: 1146 - …

Read More
MySQL: Selecting multiple fields into multiple variables in a stored procedure

Can I SELECT multiple columns into multiple variables within the same select query in MySQL? For example: DECLARE iId INT(20); DECLARE dCreate DATETIME; SELECT Id INTO iId, dateCreated INTO dCreate FROM products WHERE pName=iName; What is the correct syntax …

Read More
Mysql - How to quit/exit from stored procedure

I have very simple question but i did't get any simple code to exit from SP using Mysql. Can anyone share with me how to do that? CREATE PROCEDURE SP_Reporting(IN tablename VARCHAR(20)) BEGIN IF tablename IS NULL THEN #…

Read More