Dynamic SQL

So far, all SQL queries we have written were static — their structure was fixed at design time.

In real-world systems, queries often need to be constructed dynamically based on user input, conditions, or application logic.

This is where Dynamic SQL comes into play.

What is Dynamic SQL?

Dynamic SQL refers to SQL statements that are built as strings and executed at runtime.

Instead of writing the final query directly, we generate it dynamically and then execute it.

Why Do We Need Dynamic SQL?

Dynamic SQL is useful when:

• Table names or column names change dynamically
• Optional filters must be applied conditionally
• Complex reporting queries are generated
• Applications build queries at runtime

Static SQL cannot handle these cases cleanly.

Where is Dynamic SQL Used?

Dynamic SQL is commonly used inside:

• Stored procedures
• Application backend code
• Reporting systems

In MySQL, dynamic SQL is executed using prepared statements.

Prepared Statements Overview

MySQL provides prepared statements to safely execute dynamic SQL.

Main steps:

1. Prepare the SQL statement
2. Execute the statement
3. Deallocate the statement

Basic Dynamic SQL Example

SET @sql = 'SELECT * FROM employees';
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
  

Here, the SQL query is stored in a variable and executed dynamically.

Dynamic SQL with WHERE Clause

Example: Build a query with a dynamic condition.

SET @dept = 1;
SET @sql = CONCAT(
  'SELECT * FROM employees WHERE department_id = ',
  @dept
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
  

Dynamic SQL Inside a Stored Procedure

Dynamic SQL is often wrapped inside procedures.

DELIMITER $$

CREATE PROCEDURE get_employees_dynamic(IN dept_id INT)
BEGIN
  SET @sql = CONCAT(
    'SELECT name, salary FROM employees WHERE department_id = ',
    dept_id
  );

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END $$

DELIMITER ;
  

Executing the Procedure

CALL get_employees_dynamic(2);
  

Using Dynamic Column Names

Dynamic SQL is the only way to handle dynamic column names.

SET @column = 'salary';
SET @sql = CONCAT(
  'SELECT name, ', @column, ' FROM employees'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
  

Dynamic SQL vs Static SQL

SQL Injection Risk ⚠️

Dynamic SQL can be dangerous if user input is not handled properly.

Improper dynamic SQL can lead to SQL injection attacks.

Best practices:

• Never concatenate raw user input
• Validate and sanitize inputs
• Use prepared statements properly
• Limit database permissions

When Should You Use Dynamic SQL?

Use dynamic SQL when:

• Query structure must change dynamically
• Static SQL is not feasible
• Flexibility outweighs complexity

Avoid it when static SQL can solve the problem.

Common Beginner Mistakes

• Overusing dynamic SQL unnecessarily
• Ignoring SQL injection risks
• Forgetting to DEALLOCATE prepared statements
• Making queries hard to debug

Real-World Use Cases

• Dynamic reporting systems
• Search filters with optional fields
• Admin dashboards
• Metadata-driven queries

What’s Next?

In the next lesson, we will explore Query Optimization, focusing on how to analyze and improve SQL performance.