DYNAMIC SQLProgrammability
Buy Me A Coffee

Constructing and executing a SQL statement as a string at runtime, allowing the query structure to vary based on input.

Pre-compiled SQL templates that can be executed with different parameter values, improving performance for repeated queries and preventing SQL injection. Parameters are bound rather than concatenated, separating code from data.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 5.0 PREPARE/EXECUTE/DEALLOCATE PREPARE syntax works inside stored routines and at the session level. Parameterized via USING.
PostgreSQL ✓ Supported 7.4 EXECUTE inside PL/pgSQL runs dynamic SQL. USING clause passes parameters safely. format() with %I/%L helps prevent injection.
SQL Server ✓ Supported 2005 sp_executesql is preferred over EXEC(@sql) because it supports parameterization and plan reuse. Always use sp_executesql for user-supplied values.
Oracle ✓ Supported 8i EXECUTE IMMEDIATE is the standard PL/SQL mechanism. DBMS_SQL package provides a lower-level cursor API for cases where column count is unknown at compile time.
SQLite ✗ Not Supported — No server-side dynamic SQL. The application layer must construct and submit statements via the sqlite3 C API or a driver.

Details

Dynamic SQL is a necessary evil for cases like pivot columns, table-name parameters, or optional filter clauses. The biggest portability concern is SQL injection: always use parameterized queries (USING / sp_executesql parameters / EXECUTE IMMEDIATE USING) rather than string concatenation with user input. PostgreSQL's format() function with %I (identifier quoting) and %L (literal quoting) is particularly ergonomic. SQL Server's sp_executesql additionally enables plan caching since the parameterized template is stable across calls. Oracle's DBMS_SQL package is needed when the number or types of output columns are unknown at compile time — otherwise EXECUTE IMMEDIATE is simpler. MySQL's PREPARE/EXECUTE works at the session level without a stored procedure, which is unique among these engines.

Standard Syntax

-- SQL Server / T-SQL EXEC sp_executesql N'SELECT * FROM ' + @tbl;

Version Support

MySQL: Since 5.0 PostgreSQL: Since 7.4 SQL Server: Since 2005 Oracle: Since 8i SQLite: Not supported

Per-Database Syntax & Notes

MySQL

PREPARE/EXECUTE/DEALLOCATE PREPARE syntax works inside stored routines and at the session level. Parameterized via USING.

SET @sql = CONCAT('SELECT * FROM ', tbl_name); PREPARE stmt FROM @sql; EXECUTE stmt USING @param; DEALLOCATE PREPARE stmt;

PostgreSQL

EXECUTE inside PL/pgSQL runs dynamic SQL. USING clause passes parameters safely. format() with %I/%L helps prevent injection.

DO $$ DECLARE sql TEXT; BEGIN sql := format('SELECT * FROM %I WHERE id = %L', tbl, val); EXECUTE sql; END; $$;

SQL Server

sp_executesql is preferred over EXEC(@sql) because it supports parameterization and plan reuse. Always use sp_executesql for user-supplied values.

DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM dbo.Orders WHERE id = @id'; EXEC sp_executesql @sql, N'@id INT', @id = 42;

Oracle

EXECUTE IMMEDIATE is the standard PL/SQL mechanism. DBMS_SQL package provides a lower-level cursor API for cases where column count is unknown at compile time.

DECLARE sql_str VARCHAR2(200); BEGIN sql_str := 'UPDATE orders SET status = :1 WHERE id = :2'; EXECUTE IMMEDIATE sql_str USING 'closed', 99; END;