Dynamic table names in a stored procedure function

Question

I wrote a stored procedure function to get the name from the table. The problem is that I want the table name to be passed as a parameter (for this function, you need to use several different tables):

DELIMITER $$ CREATE DEFINER=`root`@`localhost` FUNCTION `getName`(tableName VARCHAR(50), myId INT(11)) RETURNS VARCHAR(50) begin DECLARE myName VARCHAR(50); SELECT 'name' INTO myName FROM tableName WHERE id=myId; RETURN myName; end 

This method has an error because the variable name "tableName" is used instead of the actual value of the variable.

I can get around this problem in the procedure using CONCAT as follows:

  SET @GetName = CONCAT(" SELECT 'name' FROM ",tableName," WHERE id=",myId,"; "); PREPARE stmt FROM @GetName; EXECUTE stmt; 

... but when I try to do this in a function, I get a message:

Dynamic SQL is not allowed in a stored function or trigger

I tried to use the procedure instead, but I could not get it to simply return the value, as the function does.

So, can anyone see a way around this problem. It is really incredibly simple.

Answer 1

If you want to load an SQL statement using identifiers, you need to use prepared statements; but prepared statements cannot be used in functions. This way you can create a stored procedure with the OUT parameter -

 CREATE PROCEDURE getName (IN tableName VARCHAR(50), IN myId INT(11), OUT myName VARCHAR(50)) BEGIN SET @GetName = CONCAT('SELECT name INTO @var1 FROM ', tableName, ' WHERE id=', myId); PREPARE stmt FROM @GetName; EXECUTE stmt; SET myName = @var1; END 

Using example -

 SET @tableName = 'tbl'; SET @myId = 1005; SET @name = NULL; CALL getName(@tableName, @myId, @name); SELECT @name; 

Answer 2

I agree that we can use the stored procedure with the OUT parameter, but what if we create this storage procedure for a web application where there is none. other instances will use the same procedure and change this variable OUT. In mysql, the OUT variable is like a global variable. One instance will change its value, and before our program reads its value, it will change with another instance on the Internet. What is the other solution? I ran into the same problem, I want to have a return value + I want to use a dynamic table name.