CODEWITHSUNDEEP
mysql
aHunter
aHunter

Reputation: 3530

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

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.

Upvotes: 3

Views: 11506

Answers (1)

a1ex07
a1ex07

Reputation: 37382

Instead of stmt varchar(255) use @stmt:

...
 DECLARE datereg DATETIME;
  SET @stmt = concat(
  'SELECT dateT FROM', dbTable, 'ORDER BY dateT DESC LIMIT 1');
  ....

Upvotes: 3

Related Questions