Search code examples
mysqlsqlmysql-error-1054

generic Mysql stored procedure

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'
Solution

  • 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.