"Dynamic SQL" refers to code that is generated programmatically at runtime and then executed. It is often necessary when you need to write queries where the table names, column names, or WHERE clauses aren't known until the user interacts with your application.
The Classic Use Case: "Search Anything"
Imagine a search form where a user can filter by Name, OR Age, OR City, OR all three.
In static SQL, you end up with a mess of OR conditions handling NULLs:
SELECT * FROM users
WHERE
(@name IS NULL OR name = @name)
AND (@age IS NULL OR age = @age)
AND (@city IS NULL OR city = @city);
This is known as the "Kitchen Sink" query. It works, but performance is often terrible because the database can't optimize the execution plan for all variations.
With Dynamic SQL, you build the string: "SELECT * FROM users WHERE 1=1" and then append " AND name = ?" only if the name parameter is provided. This yields a clean, optimized query every time.
The Massive Risk: SQL Injection
The #1 rule of Dynamic SQL: NEVER concatenate user input directly into your query string.
-- ❌ DANGEROUS WAY
SET @sql = 'SELECT * FROM users WHERE name = ''' + @UserName + '''';
EXEC(@sql);
If @UserName is ' OR '1'='1, you just dumped your entire database.
The Safe Way: Sp_executesql and Prepare
Always use mechanisms that separate code from data. In SQL Server, use sp_executesql. In PostgreSQL/MySQL, use PREPARE or your application's parameterized query function.
-- ✅ SAFE WAY (SQL Server)
SET @sql = 'SELECT * FROM users WHERE name = @name';
EXEC sp_executesql @sql, N'@name NVARCHAR(50)', @name = @UserName;
Even though the query structure is dynamic, the values are passed as parameters.
Handling Dynamic Identifiers
What if the table name itself is dynamic? You can't parameterize identifiers (e.g., FROM @tablename is invalid).
You must whitelist the allowed table names or strictly sanitize them.
-- ✅ SAFE IDENTIFIER HANDLING
-- Use QUOTENAME() (SQL Server) or quote_ident() (Postgres)
SET @sql = 'SELECT * FROM ' + QUOTENAME(@TableName);
Interactive Playground
In this example, we show a clean way to handle multiple optional filters without building a dynamic string (since we can't run dynamic execute commands easily in SQLite web view), using the 1=1 pattern logic which is the foundation of dynamic query logic.
Conclusion
Dynamic SQL is a powerful tool for building flexible applications and reports. However, it requires a disciplined approach to security.
- Parameterize values always.
- Quote/Sanitize identifiers.
- Review generated SQL logs to ensure performance.