How Can I Access Dynamically Named Tables Within a SQL Function?

Dynamic Table Names in Stored Procedure Function

In SQL, functions are immutable and cannot modify data. Therefore, prepared statements cannot be used within functions, which poses a challenge when attempting to access tables with dynamic names.

To overcome this issue, a stored procedure can be employed. Stored procedures can utilize prepared statements and OUT parameters to return values.

Here's an example of a stored procedure that can retrieve a name from a dynamically specified table:

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>

To use the procedure, IN parameters are set with the table name and ID, while an OUT parameter is used to retrieve the name.

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

The above is the detailed content of How Can I Access Dynamically Named Tables Within a SQL Function?. For more information, please follow other related articles on the PHP Chinese website!