How to Retrieve Data from Dynamically Specified Tables in Stored Procedures?

Dynamic Table Names in Stored Procedure Functions

Problem Statement:

The objective is to create a stored procedure function that retrieves a value from a table, where the table name is provided dynamically as a parameter. However, attempts to dynamically generate the SQL statement within the function result in a "Dynamic SQL is not allowed..." error. The requirement is to find a method to overcome this limitation and achieve the desired result.

Solution:

While dynamic SQL is not permitted within stored procedure functions, it is possible to utilize prepared statements to dynamically construct and execute SQL statements. Unfortunately, prepared statements cannot be directly used in functions.

An alternative approach is to create a stored procedure with an 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>

This procedure accepts input parameters tableName and myId, and sets the myName OUT parameter to the retrieved value. The procedure dynamically constructs the SQL statement and uses a prepared statement for execution.

To use this procedure, follow these steps:

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

This solution allows for the dynamic retrieval of values from different tables within a stored procedure.

The above is the detailed content of How to Retrieve Data from Dynamically Specified Tables in Stored Procedures?. For more information, please follow other related articles on the PHP Chinese website!