Dynamic SQL — Building and Executing SQL on the Fly

A report accepts a city value and an approved sort choice from the user. Both affect the request, but they cannot be handled with the same substitution rule.

Dynamic SQL constructs or prepares a statement at runtime. The SQL structure and its data values require different handling.

Bind data values through supported parameter markers. Identifiers and sort directions are part of SQL structure and usually cannot be replaced by a value parameter; choose them from an explicit allowlist instead. MySQL PREPARE and EXECUTE implement server-side prepared statements, with restrictions inside stored programs.

Related concepts: Prevent SQL Injection and SQL with Python.

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.

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

MySQL 8.4 · prepared statement

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');

MySQL 8.4 · prepared statement

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

MySQL 8.4 · prepared statement

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

MySQL 8.4 · prepared statement

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 table, column, or filter values are determined at runtime
• When building advanced search or reporting systems
• When writing reusable admin tools or dashboards

• 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

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

Concatenating an untrusted value into SQL is unsafe. Escaping manually is not a substitute for parameter binding. Dynamic SQL is unnecessary when one static parameterized query already expresses the task.

Prepare a MySQL query with one city value parameter and execute it for Pune.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

MySQL 8.4 · prepared statement

PREPARE practice_report FROM 'SELECT name FROM practice_students WHERE city = ? ORDER BY roll_no';
SET @practice_city = 'Pune';
EXECUTE practice_report USING @practice_city;
DEALLOCATE PREPARE practice_report;

The statement structure stays fixed while the city value is supplied separately. A sort-column name would instead need selection from trusted SQL choices.

Run this engine-specific script on a fresh MySQL 8.4 practice schema with the required routine privileges. It is not executable in SQLite.

Dynamic SQL constructs or prepares a statement at runtime. The SQL structure and its data values require different handling.

The statement structure stays fixed while the city value is supplied separately. A sort-column name would instead need selection from trusted SQL choices.

Continue your understanding with Prevent SQL Injection and SQL with Python.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.