Dynamic SQL
Building and Executing SQL on the Fly

Introduction

What if your SQL query depends on user input or varies based on certain conditions? You can't hardcode every case. That’s where Dynamic SQL comes in. It lets you construct and execute SQL statements at runtime — adding flexibility, interactivity, and control.

What is Dynamic SQL?

Dynamic SQL is a SQL statement created and executed at runtime rather than being hardcoded into the procedure. You use it when the structure of your query is not fixed in advance — for example, when filtering by variable columns, table names, or sorting criteria.

MySQL Syntax Overview

• PREPARE stmt_name FROM @sql
• EXECUTE stmt_name
• DEALLOCATE PREPARE stmt_name

Sample Table – students

CREATE TABLE students (
  roll_no INT,
  name VARCHAR(50),
  marks INT,
  city VARCHAR(30)
);

INSERT INTO students VALUES
(1, 'Aarav Sharma', 85, 'Delhi'),
(2, 'Diya Iyer', 92, 'Chennai'),
(3, 'Sneha Patil', 78, 'Pune'),
(4, 'Mehul Agarwal', 90, 'Delhi');

1. Basic Dynamic SQL – Select by City

SET @city_name = 'Delhi';
SET @sql = CONCAT('SELECT name, marks FROM students WHERE city = "', @city_name, '"');

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

name           | marks
----------------+-------
Aarav Sharma   | 85
Mehul Agarwal  | 90

2. Sorting Dynamically

SET @sort_column = 'marks';
SET @sql = CONCAT('SELECT name, marks FROM students ORDER BY ', @sort_column, ' DESC');

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

name           | marks
----------------+-------
Diya Iyer      | 92
Mehul Agarwal  | 90
Aarav Sharma   | 85
Sneha Patil    | 78

3. Inside a Stored Procedure

DELIMITER $$

CREATE PROCEDURE get_students_by_city(IN p_city VARCHAR(30))
BEGIN
  SET @sql = CONCAT('SELECT name, marks FROM students WHERE city = "', p_city, '"');
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END$$

DELIMITER ;

Call it:

CALL get_students_by_city('Pune');

When to Use Dynamic SQL

• When table, column, or filter values are determined at runtime
• When building advanced search or reporting systems
• When writing reusable admin tools or dashboards

Best Practices

• Always sanitize input to avoid SQL injection risks
• Use prepared statements instead of directly executing dynamic strings
• Use stored procedures to encapsulate dynamic SQL safely

Real-World School Use Cases

• Generate student reports by city or class dynamically
• Create dashboards where the admin selects filter criteria
• Allow dynamic sorting and pagination of student data

Summary

Dynamic SQL adds the power of flexibility to your SQL toolbox. Instead of writing rigid queries, you can build smart, context-aware scripts that adapt to input or conditions. In a school database, this means more intelligent reports, user-driven filters, and robust admin utilities.

What’s Next?

Next up: SQL Optimization Techniques — master the art of making your queries fast, efficient, and scalable.