How Can I Execute Dynamic SQL Queries in MySQL Without Using EXEC or EXECUTE?

Executing Strings in MySQL

One of the common tasks in programming is the ability to dynamically generate and execute SQL queries. In MySQL, this functionality is not as straightforward as in some other languages like SQL Server, which provides the EXEC command for this purpose.

Problem:

A user wants to convert a stored procedure in MSSQL that uses a varchar as a query. However, EXEC and EXECUTE are not supported in MySQL, and CALL also fails to work.

Solution:

While MySQL does not have an exact equivalent of JavaScript's eval function, there is a workaround using a combination of dynamic SQL and stored procedures. Here's how it can be achieved:

1. Prepare the Dynamic Query: Use a derived table to concatenate the values to be inserted, resulting in a single string containing the SQL query.
2. Create a Prepared Statement: Assign the dynamic query to a prepared statement variable using the PREPARE statement.
3. Execute the Prepared Statement: Execute the prepared statement using the EXECUTE statement.
4. Deallocate the Prepared Statement: After executing the query, deallocate the prepared statement variable using the DEALLOCATE PREPARE statement.

Example:

The following code snippet illustrates the solution:

SET @queryString = (
SELECT CONCAT('INSERT INTO user_group (`group_id`,`user_id`) VALUES ', www.vals) as res FROM (
    SELECT GROUP_CONCAT(qwe.asd SEPARATOR ',') as vals FROM ( 
           SELECT CONCAT('(59,', user_id, ')') as asd FROM access WHERE residency = 9 
    ) as qwe 
) as www
);

PREPARE stmt FROM @queryString;
EXECUTE stmt;
DEALLOCATE PREPARE stmt; 
SET @asd = NULL;

The above is the detailed content of How Can I Execute Dynamic SQL Queries in MySQL Without Using EXEC or EXECUTE?. For more information, please follow other related articles on the PHP Chinese website!