Variable Table Name MySQL Stored Function

Question

I have several MySQL tables that maintains a tree structure of records. Each record have an ID and a Parent field. I want to write a stored function to get the parent ID, given a record ID.

The following is my first attempt, and it's incorrect. My problem is I do not know how to use variable table name.

delimiter $$

create function parent(
    tableName varchar(15),
    nodeId    int
) returns int
begin
    declare p int;
    select parent into p from tableName where id=nodeId;
    return p;
end$$

Please help. Thanks!

Multiple tables with the same structure are a bad idea. You should be storing them in one table with an additional column specifying the "tablename". — Gordon Linoff
– Gordon Linoff, Commented Feb 25, 2014 at 2:22
That's true, but this is for research performance evaluation. Basically, there are several tables with the same structure but different parameters. I use these tables to measure the performance of my algorithm. — Jeff
– Jeff, Commented Feb 25, 2014 at 2:25
@Jeff But once you have dynamic SQL in the mix all your measurements will be off anyway — lc.
– lc., Commented Feb 25, 2014 at 2:26

Jeff · Accepted Answer · 2014-02-25 04:14:18Z

2

After some research, apparently a stored function will not work in this case due to the fact stored functions cannot execute dynamic SQL. I change my implementation to a stored procedure.

delimiter $$

create procedure parent(tableName varchar(15), nodeId int)
begin
    set @s := concat('select parent from ', tableName, ' where id =', nodeId);
    prepare query from @s;
    execute query;
    deallocate prepare query;
end$$

delimiter ;

answered Feb 25, 2014 at 4:14

Jeff

3372 gold badges4 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

AntonioCS Over a year ago

So how do you get the value now?

qdev Over a year ago

@AntonioCS That's a procedure, it will output the sql result. If you want only the value to use it in another SQL (and you're sure your procedure extracts one value) I've added an output parameter to the procedure, and then created a function which actually calls the procedure and returns the output, I had to use this trick in order to build a recursive function, and it works ;)

Collectives™ on Stack Overflow

Variable Table Name MySQL Stored Function

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related