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