Can I use a MySQL PREPARE statement in a function to create a query with a variable table name

Posted by aHunter on Stack Overflow See other posts from Stack Overflow or by aHunter
Published on 2010-06-16T21:47:20Z Indexed on 2010/06/16 21:52 UTC
Read the original article Hit count: 231

Filed under:

I want to create a function that has a select query inside that can be used against multiple database tables but I can not use a variable as the table name. Can I get around this using a PREPARE statement in the function?

An Example:

FUNCTION `TESTFUNC`(dbTable VARCHAR(25)) RETURNS bigint(20)
BEGIN

    DECLARE datereg DATETIME;
    DECLARE stmt VARCHAR(255);

    SET stmt := concat(
      'SELECT dateT FROM', dbTable, 'ORDER BY dateT DESC LIMIT 1');

    PREPARE stmt FROM @stmt;

    EXECUTE stmt;

    RETURN dateT;

END $$

Thanks in advance for any input.

© Stack Overflow or respective owner

Related posts about mysql