32

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?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
randomx
  • 2,357
  • 1
  • 21
  • 30
  • 1
    Even if this were possible, this isn't a path you should go down. Either build out the s'procs with the sql you really want, or just go ahead and embed the ad hoc sql in your code. – NotMe Jun 04 '10 at 20:00
  • 8
    @Chris You can see the intent and the power here, right? Passing a table name to a generic function. I see it as perfectly legit and needed. Help me understand why it's bad to control a result set from a sproc? From what you're saying, if I have a .NET developer and a PHP developer I should ask them to both write their own code to get the same result set? – randomx Jun 04 '10 at 20:07
  • I see the intent, and it's flawed. I highly suggest you read the following: http://www.sommarskog.se/dynamic_sql.html Although it covers ms sql server, the lessons are the same. – NotMe Jun 07 '10 at 01:45
  • And here is another informative link: http://sqlskills.com/BLOGS/KIMBERLY/post/Little-Bobby-Tables-SQL-Injection-and-EXECUTE-AS.aspx – NotMe Jun 07 '10 at 01:48

2 Answers2

41

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 $$
a1ex07
  • 36,826
  • 12
  • 90
  • 103
  • +1: You were first, & correct - you have to use Prepared Statements (MySQL's dynamic SQL): http://rpbouman.blogspot.com/2005/11/mysql-5-prepared-statement-syntax-and.html – OMG Ponies Jun 04 '10 at 20:05
  • 3
    `CALL GetNumRows(gps_location)` Returns: `#1054 - Unknown column 'gps_location' in 'field list'` – grepsedawk Mar 24 '14 at 06:55
  • @a1ex07 but if I want to declare a cursor, and I need to concat and call procedre in one statement? – VB_ May 26 '14 at 09:07
  • 1
    @V_B: I think you need to ask a separate question, this one is quite old, and I don't see how having/not having cursors related to the original question... – a1ex07 May 26 '14 at 14:35
  • @Pachonk gps_location would be something SQL can use at this point, a column name, variable... The function concats a string, so that's what you must pass in. – Daniel Aug 21 '18 at 13:41
  • 1
    Link to MySQL's prepared statement page is dead. This one works instead: [Prepared Statements in MySQL 8.0](https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html) – Diggi55 Sep 01 '21 at 09:32
4

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 ;
Getachew Mulat
  • 41
  • 2