generic Mysql stored procedure

Question

Hi,

I have the fallowing stored procedure:

CREATE PROCEDURE `get`(IN tb VARCHAR(50), IN id INTEGER)
BEGIN
    SELECT * FROM tb  WHERE Indx = id;
END//

When I call get(user,1) I get the following:

ERROR 1054 (42S22): Unknown column 'user' in 'field list'

Answer 1

can you call it as get('user',1) ?

IN tb VARCHAR(50) makes it expect a 'string', you are passing something the SQL parser interpretes as the field user and that is not know.

Answer 2

You can't use a variable as a table name in SQL because it compiles that in when the procedure is compiled. Try using prepared statements:

CREATE PROCEDURE `get`(IN tb VARCHAR(50), IN id INTEGER)
BEGIN
    PREPARE stmt1 FROM CONCAT('SELECT * FROM ', tb, ' WHERE Indx = id');
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
END//

Note however that this is going to be slower.