MySQL Dynamic SQL
Introduction
Dynamic SQL allows you to build and execute SQL statements at runtime rather than having them predefined during stored procedure creation. This provides flexibility when you need to create queries where the exact structure isn't known until the procedure runs.
In standard stored procedures, SQL statements are fixed. With dynamic SQL, you can:
- Build SQL statements using variables
- Change table names dynamically
- Modify WHERE clauses based on input parameters
- Create flexible sorting and filtering logic
This tutorial will show you how to use MySQL's dynamic SQL capabilities effectively within stored procedures.
Understanding MySQL Dynamic SQL
What is Dynamic SQL?
Dynamic SQL refers to SQL statements that are constructed and executed at runtime. Unlike static SQL, which is coded directly into your stored procedures, dynamic SQL is built as a string and then executed using special commands.
When to Use Dynamic SQL
Dynamic SQL is particularly useful when:
- The table name is not known in advance
- The columns to be selected depend on user input
- The sorting order or filtering conditions vary
- You need to create generic procedures that work across multiple tables
The Basic Mechanics
MySQL implements dynamic SQL through three main commands:
PREPARE- Creates a statement from a stringEXECUTE- Runs the prepared statementDEALLOCATE PREPARE- Frees the resources used by the prepared statement
Let's see a simple example:
DELIMITER //
CREATE PROCEDURE simple_dynamic_query(IN table_name VARCHAR(100))
BEGIN
SET @sql = CONCAT('SELECT * FROM ', table_name, ' LIMIT 5');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
DELIMITER ;
Here's what happens when you call this procedure:
CALL simple_dynamic_query('employees');
Output:
+--------+------------+------------+-----------+--------+------------+
| emp_id | first_name | last_name | email | salary | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 1001 | John | Smith | [email protected] | 65000 | 2018-03-15 |
| 1002 | Mary | Johnson | [email protected] | 78000 | 2016-11-08 |
| 1003 | James | Williams | [email protected] | 55000 | 2019-06-22 |
| 1004 | Patricia | Brown | [email protected] | 82000 | 2017-09-01 |
| 1005 | Michael | Davis | [email protected] | 69000 | 2020-01-10 |
+--------+------------+------------+-----------+--------+------------+
Building Dynamic SQL Statements
String Concatenation
The most common way to build dynamic SQL is through string concatenation using the CONCAT() function:
SET @sql = CONCAT('SELECT * FROM employees WHERE department_id = ', department_id);
Using User Variables
User-defined variables (prefixed with @) play a crucial role in dynamic SQL:
SET @table_name = 'employees';
SET @sql = CONCAT('SELECT COUNT(*) FROM ', @table_name);
Parameter Passing in Dynamic SQL
For passing parameters to dynamic SQL statements, you'll use the USING clause with EXECUTE:
DELIMITER //
CREATE PROCEDURE dynamic_query_with_params(IN emp_id INT)
BEGIN
SET @sql = 'SELECT * FROM employees WHERE employee_id = ?';
SET @id = emp_id;
PREPARE stmt FROM @sql;
EXECUTE stmt USING @id;
DEALLOCATE PREPARE stmt;
END //
DELIMITER ;
When called:
CALL dynamic_query_with_params(1003);
Output:
+--------+------------+------------+-----------+--------+------------+
| emp_id | first_name | last_name | email | salary | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 1003 | James | Williams | [email protected] | 55000 | 2019-06-22 |
+--------+------------+------------+-----------+--------+------------+
Practical Examples of Dynamic SQL
Example 1: Dynamic Sorting
This procedure allows you to sort any table by any column in any direction:
DELIMITER //
CREATE PROCEDURE sort_table(
IN table_name VARCHAR(100),
IN sort_column VARCHAR(100),
IN sort_direction VARCHAR(4)
)
BEGIN
-- Validate sort direction
IF sort_direction NOT IN ('ASC', 'DESC') THEN
SET sort_direction = 'ASC';
END IF;
-- Build and execute the query
SET @sql = CONCAT('SELECT * FROM ', table_name,
' ORDER BY ', sort_column, ' ', sort_direction);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
DELIMITER ;
Using the procedure:
CALL sort_table('employees', 'salary', 'DESC');
Output:
+--------+------------+------------+------------+--------+------------+
| emp_id | first_name | last_name | email | salary | hire_date |
+--------+------------+------------+------------+--------+------------+
| 1004 | Patricia | Brown | [email protected] | 82000 | 2017-09-01 |
| 1002 | Mary | Johnson | [email protected] | 78000 | 2016-11-08 |
| 1005 | Michael | Davis | [email protected] | 69000 | 2020-01-10 |
| 1001 | John | Smith | [email protected] | 65000 | 2018-03-15 |
| 1003 | James | Williams | [email protected] | 55000 | 2019-06-22 |
+--------+------------+------------+------------+--------+------------+
Example 2: Dynamic Filtering
This procedure builds a flexible WHERE clause:
DELIMITER //
CREATE PROCEDURE filter_employees(
IN filter_by VARCHAR(100),
IN filter_value VARCHAR(100)
)
BEGIN
SET @sql = CONCAT('SELECT * FROM employees WHERE ',
filter_by, ' = ?');
PREPARE stmt FROM @sql;
EXECUTE stmt USING filter_value;
DEALLOCATE PREPARE stmt;
END //
DELIMITER ;
Using the procedure:
CALL filter_employees('department', 'IT');
Output:
+--------+------------+------------+------------+--------+------------+
| emp_id | first_name | last_name | email | salary | department |
+--------+------------+------------+------------+--------+------------+
| 1001 | John | Smith | [email protected] | 65000 | IT |
| 1005 | Michael | Davis | [email protected] | 69000 | IT |
+--------+------------+------------+------------+--------+------------+
Example 3: Dynamic Table Creation
You can even create tables dynamically:
DELIMITER //
CREATE PROCEDURE create_backup_table(IN original_table VARCHAR(100))
BEGIN
DECLARE current_date VARCHAR(10);
-- Get current date in YYYYMMDD format
SET current_date = DATE_FORMAT(NOW(), '%Y%m%d');
-- Create backup table name
SET @backup_table = CONCAT(original_table, '_backup_', current_date);
-- Build and execute CREATE TABLE statement
SET @sql = CONCAT('CREATE TABLE ', @backup_table,
' SELECT * FROM ', original_table);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- Return confirmation message
SELECT CONCAT('Backup created: ', @backup_table) AS message;
END //
DELIMITER ;
Using the procedure:
CALL create_backup_table('employees');
Output:
+------------------------------------+
| message |
+------------------------------------+
| Backup created: employees_20230425 |
+------------------------------------+
Advanced Dynamic SQL Techniques
Error Handling in Dynamic SQL
Error handling is important when dealing with dynamic SQL since errors might only be detected at runtime:
DELIMITER //
CREATE PROCEDURE safe_dynamic_query(IN table_name VARCHAR(100))
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SELECT 'Error: The table does not exist or you do not have permissions' AS message;
END;
SET @sql = CONCAT('SELECT * FROM ', table_name, ' LIMIT 5');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
DELIMITER ;
Creating Conditional Dynamic SQL
You can build different SQL statements based on conditions:
DELIMITER //
CREATE PROCEDURE conditional_query(
IN table_name VARCHAR(100),
IN include_inactive BOOLEAN
)
BEGIN
SET @sql = CONCAT('SELECT * FROM ', table_name);
IF NOT include_inactive THEN
SET @sql = CONCAT(@sql, ' WHERE status = "active"');
END IF;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
DELIMITER ;
Best Practices for Dynamic SQL
-
Security Considerations
Dynamic SQL can be vulnerable to SQL injection attacks. Always validate user inputs and consider using prepared statements with parameters:
sql-- UNSAFE: Direct concatenation
SET @sql = CONCAT('SELECT * FROM users WHERE username = "', user_input, '"');
-- SAFER: Using parameters
SET @sql = 'SELECT * FROM users WHERE username = ?';
PREPARE stmt FROM @sql;
EXECUTE stmt USING @user_input; -
Performance Considerations
Dynamic SQL statements need to be parsed and compiled each time they're executed, which can impact performance. Consider:
- Using dynamic SQL only when necessary
- Caching results when possible
- Using query hints when appropriate
-
Maintainability
Dynamic SQL can be harder to debug. Follow these practices:
- Comment your code thoroughly
- Log the generated SQL for debugging
- Break complex statements into manageable chunks
Common Use Cases for Dynamic SQL
-
Generic Data Export Procedures
sqlDELIMITER //
CREATE PROCEDURE export_table_data(
IN table_name VARCHAR(100),
IN output_format VARCHAR(10)
)
BEGIN
CASE output_format
WHEN 'CSV' THEN
SET @sql = CONCAT('SELECT * FROM ', table_name, ' INTO OUTFILE "/tmp/',
table_name, '.csv" FIELDS TERMINATED BY "," ENCLOSED BY \'"\' LINES TERMINATED BY "\n"');
WHEN 'JSON' THEN
SET @sql = CONCAT('SELECT JSON_ARRAYAGG(JSON_OBJECT(*)) FROM ', table_name,
' INTO OUTFILE "/tmp/', table_name, '.json"');
ELSE
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid output format specified';
END CASE;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
DELIMITER ; -
Dynamic Reporting
sqlDELIMITER //
CREATE PROCEDURE generate_sales_report(
IN time_period VARCHAR(20),
IN group_by_field VARCHAR(50)
)
BEGIN
SET @sql = 'SELECT ';
SET @sql = CONCAT(@sql, group_by_field, ', ');
CASE time_period
WHEN 'daily' THEN
SET @sql = CONCAT(@sql, 'DATE(order_date) as date, ');
WHEN 'monthly' THEN
SET @sql = CONCAT(@sql, 'MONTH(order_date) as month, ');
WHEN 'yearly' THEN
SET @sql = CONCAT(@sql, 'YEAR(order_date) as year, ');
END CASE;
SET @sql = CONCAT(@sql, 'SUM(order_amount) as total_sales FROM orders ');
SET @sql = CONCAT(@sql, 'GROUP BY ', group_by_field, ', ');
CASE time_period
WHEN 'daily' THEN
SET @sql = CONCAT(@sql, 'DATE(order_date)');
WHEN 'monthly' THEN
SET @sql = CONCAT(@sql, 'MONTH(order_date)');
WHEN 'yearly' THEN
SET @sql = CONCAT(@sql, 'YEAR(order_date)');
END CASE;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
DELIMITER ;
Summary
Dynamic SQL in MySQL stored procedures provides powerful flexibility for creating adaptable database operations. With the PREPARE, EXECUTE, and DEALLOCATE PREPARE statements, you can build SQL commands at runtime that respond to varying conditions and requirements.
Key points covered in this tutorial:
- Creating basic dynamic SQL statements
- Using parameters with dynamic SQL
- Building complex SQL with conditional logic
- Implementing proper error handling
- Following security and performance best practices
- Creating practical applications like dynamic reporting and table manipulation
While dynamic SQL offers flexibility, remember to use it judiciously. Static SQL is generally more efficient and secure when the query structure is known in advance.
Exercises
To practice your dynamic SQL skills, try these exercises:
-
Create a stored procedure that accepts a table name and column name, then returns the minimum, maximum, and average values for that column.
-
Build a procedure that creates a new table with a subset of columns from an existing table, where the columns are specified as a comma-separated parameter.
-
Create a generic search procedure that lets users search any specified table for records containing a given search term in any text column.
-
Implement a procedure that generates a pivot table dynamically based on user-specified row and column dimensions.
-
Create a stored procedure that builds and executes a dynamic JOIN between two user-specified tables based on a common column name parameter.
Additional Resources
- MySQL Official Documentation on Prepared Statements
- MySQL Stored Procedure Programming (Book by Guy Harrison and Steven Feuerstein)
- MySQL Dynamic SQL Tutorial on MySQL Tutorial
Happy coding with MySQL Dynamic SQL!
If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)