Dynamic SQL
Dynamic SQL allows you to construct and execute SQL statements dynamically at runtime.
What is Dynamic SQL?
Dynamic SQL is a technique where SQL statements are created dynamically as strings and executed at runtime.
It is useful when table names, column names, filtering conditions, or other parts of a SQL statement need to be determined dynamically.
Basic Syntax
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = 'SELECT * FROM Employees';
EXEC sp_executesql @SQL;
Example 1: Simple Dynamic SQL
In this example, a SQL query is stored inside a variable and executed using sp_executesql.
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = 'SELECT * FROM Employees';
EXEC sp_executesql @SQL;
Example 2: Dynamic WHERE Condition
A condition can also be passed dynamically to a SQL query.
DECLARE @SQL NVARCHAR(MAX);
DECLARE @Department NVARCHAR(50);
SET @Department = 'IT';
SET @SQL =
'SELECT * FROM Employees
WHERE Department = @Dept';
EXEC sp_executesql
@SQL,
N'@Dept NVARCHAR(50)',
@Dept = @Department;
Parameterized Dynamic SQL
Parameters should be used whenever possible instead of directly concatenating user input into a SQL string.
DECLARE @SQL NVARCHAR(MAX);
SET @SQL =
'SELECT *
FROM Employees
WHERE Salary > @Salary';
EXEC sp_executesql
@SQL,
N'@Salary DECIMAL(10,2)',
@Salary = 50000;
EXEC vs sp_executesql
| EXEC | sp_executesql |
|---|---|
| Can execute dynamic SQL | Can execute dynamic SQL |
| Parameter handling is limited | Supports parameters |
| String concatenation is commonly used | Parameterized queries are supported |
| Less suitable when parameters are required | Preferred for parameterized dynamic SQL |
Advantages of Dynamic SQL
- Allows SQL statements to be generated dynamically.
- Useful when table or column names are determined at runtime.
- Can be useful for dynamic filtering and reporting.
- Parameterized dynamic SQL can improve security and maintainability.
Important Points
- Avoid directly concatenating untrusted user input.
- Prefer sp_executesql with parameters when values are supplied dynamically.
- Dynamic SQL should be used only when it provides a real benefit over static SQL.
Summary
Dynamic SQL allows SQL statements to be constructed and executed at runtime.
It is useful for dynamic queries, filtering, reporting, and situations where parts of the SQL statement are determined at runtime. Parameterized sp_executesql is preferred when dynamic values are required.