How to Store Dynamic SQL Query Results in a MySQL Stored Procedure Variable?

Storing the Output of a Dynamic SQL Query in a Variable Using MySQL Stored Procedures

In MySQL stored procedures, it is sometimes necessary to execute dynamic queries and store the output into a user-defined variable. Here's how you can achieve this:

CREATE PROCEDURE procedure_name(
  IN query_text TEXT,
  OUT output_variable TEXT
)
BEGIN
  SET output_variable = '';

  SET @sql = query_text;
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END

In this procedure, we declare an input parameter query_text that contains the dynamic query to be executed, and an output parameter output_variable that will store the query's output.

We then set the output_variable to an empty string and prepare the query_text as an SQL statement using the PREPARE command. The EXECUTE statement is used to execute the prepared statement, and DEALLOCATE PREPARE is used to release the resources associated with it.

Example:

Let's say we have a dynamic query that generates the following output:

SELECT CONCAT('Value 1: ', value1, ' | ', 'Value 2: ', value2) AS output
FROM table_name
WHERE id = ?

We can create a procedure to execute this query and store the output in a variable:

CREATE PROCEDURE get_dynamic_output(
  IN input_id INT,
  OUT dynamic_output TEXT
)
BEGIN
  SET dynamic_output = '';

  SET @query = 'SELECT CONCAT('"'Value 1: '"', value1, '"" | '"', 'Value 2: '"', value2, '""') AS output FROM table_name WHERE id = ?';
  PREPARE stmt FROM @query;
  SET @id = input_id;
  EXECUTE stmt USING @id;
  DEALLOCATE PREPARE stmt;
END

To use this procedure, we can set the input_id parameter to the desired value and execute it:

CALL get_dynamic_output(1, @output_value);
SELECT @output_value;

This will print the output of the dynamic query as a single text string.

The above is the detailed content of How to Store Dynamic SQL Query Results in a MySQL Stored Procedure Variable?. For more information, please follow other related articles on the PHP Chinese website!