CODEWITHSUNDEEP
sqlmysqlstored-proceduresmysql-error-1146
randomx
randomx

Reputation: 2377

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

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?

Upvotes: 32

Views: 38964

Answers (2)

Getachew Mulat Zerihun
Getachew Mulat Zerihun

Reputation: 51

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 ;

Upvotes: 5

a1ex07
a1ex07

Reputation: 37354

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

Upvotes: 42

Related Questions