How to write a mysql function with dynamic table name?

Question

I'm trying to write a my sql function doing the following things: 1- get the table name used in join as a parameter.

but I get mysql syntax error

1064 - You have an error in your SQL syntax; check the manual that corresponds to 
  your MySQL server version for the right syntax to use near 'table DETERMINISTIC 
  BEGIN select `r`.`id` AS `id`, (case ' at line 2

This is my query

DELIMITER $$
CREATE FUNCTION getTranslation (tablename varchar(50),entity varchar(20),itemid int,lang char(3)) 
RETURNS table
DETERMINISTIC
BEGIN 
select 
    `r`.`id` AS `id`,
    (case
        when isnull(`t`.`descr`) then `r`.`descr_ml`
        else `t`.`descr`
    end) AS `descr`
from
    (tablename `r`
    left join `g001_translation` `t` ON ((`t`.`item_id` = `r`.`id`)))
END$$
DELIMITER ;

I the select part works fine with static table name by the way.

For such an operation to work, one must have multiple tables with identical schema which is usually contrary to the Principle of Orthogonal Design. This suggests that you're probably going about solving your underlying business problem the wrong way. Could you provide more context? Sounds like an XY Problem. — eggyal
– eggyal, Commented Jan 6, 2014 at 8:33
You need to write dynamic sql, which means constructing the query as a string, using prepare to parse it, and then execute to execute it. — Barmar
– Barmar, Commented Jan 6, 2014 at 8:35

Simon at The Access Group · Accepted Answer · 2014-01-06 09:07:43Z

First up as mentioned by @eggyal this isn't the best way to go about things. But it can be done by using prepared statements. I.e.

DROP PROCEDURE IF EXISTS `exampleOfPrepareStatement`;

CREATE DEFINER = `user`@`%` PROCEDURE `exampleOfPrepareStatement`(inTableName VARCHAR(100))
    MODIFIES SQL DATA
    SQL SECURITY INVOKER
BEGIN

    SET @hr1 = CONCAT('
        INSERT INTO `',inTableName,'` (
            -- fields (can use parameters same as table name if needed)
        )
        -- either VALUES () or SELECT here
    ');

    -- Prepare, execute, deallocate
    PREPARE hrStmt1 FROM @hr1;
    EXECUTE hrStmt1;
    DEALLOCATE PREPARE hrStmt1;

END;

You can of course add in field names etc. as needed, or use a SELECT or UPDATE etc. This is not ideal, but will do what you are looking for.

I have had to use this in some places before where the same maintenance is being performed on multiple tables which have different field names ( / table names ) and so instead of writing the same function 20 times, instead I use this type of stored procedure which can then be called to do the indexing etc.

As also mentioned by @eggyal , while this may do as you ask, it might not do as you need. If you can provide more information then you may get a better solution.

Mad Dog Tannen · Accepted Answer · 2014-01-06 09:07:25Z

1

Give this a try

SET @ex = CONCAT('select `r`.`id` AS `id`,(case when isnull(`t`.`descr`) then `r`.`descr_ml` else `t`.`descr` end) AS `descr` from (',tablename,' `r` left join `g001_translation` `t` ON ((`t`.`item_id` = `r`.`id`)));');
    PREPARE stmt FROM @ex;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

You will notice that ',tablename,' will use the parameter passed.

answered Jan 6, 2014 at 9:07

Mad Dog Tannen

7,2505 gold badges34 silver badges55 bronze badges

1 Comment

Mahefa Over a year ago

And how to get the data from execute?

Collectives™ on Stack Overflow

How to write a mysql function with dynamic table name?

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related