Dynamic Table Names in Stored Procedure Function

In MySQL, stored procedures and functions provide a powerful mechanism for performing complex operations within the database. However, when working with dynamic table names, certain limitations arise.

Dynamic Table Names in Functions

To retrieve data from a table using a function, you can utilize a query such as:

SELECT
  'name' INTO myName
FROM
  tableName
WHERE
 >

However, using this approach with dynamic table names will encounter an error due to the substitution of the actual table name with the variable name tableName.

To work around this issue, a prepared statement technique is commonly employed:

SET @GetName = CONCAT("
  SELECT
    'name'
  FROM
    ", tableName, "
  WHERE
   >

Unfortunately, this method is not supported in stored procedure functions, as MySQL prohibits dynamic SQL in such contexts.

Dynamic Table Names in Procedures

As an alternative, you can create a stored procedure with an OUT parameter instead:

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>

Usage Example

To invoke this procedure with dynamic table names, you can use the following syntax:

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

This method allows you to dynamically access data from different tables within a stored procedure, providing greater flexibility in your database operations.