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'

Answers


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.


Need Your Help

Treat any file like a bit stream for encryption/decryption

c++ encryption file-io

This should be a simple enough task, but I haven't been able to figure it out, as I've been away from C++ awhile.

Rapidly learn InDesign scripting?

javascript scripting adobe indesign

In the middle of a large publishing project, and the sheer scale is weighing me down. Many deadlines coming down the pipe, and most of my trouble is tedium and repetitive tasks. I need to pick up...