How to Access Tables Dynamically in Stored Procedure Functions?

Dynamic Table Names in Stored Procedure Function

Question:

How can a stored procedure function access a table dynamically based on a passed parameter, avoiding the error "Dynamic SQL is not allowed in stored function or trigger"?

Answer:

While dynamic SQL is not allowed in stored functions, there are alternative approaches. One option is to create a stored procedure with an OUT parameter and use prepared statements to dynamically build the SQL statement.

Solution:

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>

Example Usage:

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

This workaround provides a means to dynamically access tables in stored procedure functions using prepared statements and OUT parameters.

The above is the detailed content of How to Access Tables Dynamically in Stored Procedure Functions?. For more information, please follow other related articles on the PHP Chinese website!