Dynamic SQL

Build and execute SQL statements dynamically.

Introduction

Dynamic SQL is a technique in SQL that allows you to build and execute SQL statements dynamically at runtime. Instead of hardcoding the queries, you construct them as strings and execute them using system functions. This is especially useful when the exact structure of the query isn’t known until runtime, such as in cases where you have variable filtering conditions or want to perform different operations based on input parameters.

In Microsoft SQL Server, dynamic SQL is implemented using the sp_executesql system stored procedure or the EXEC command.

Why Use Dynamic SQL?

Dynamic SQL becomes handy in situations like:

  • Dynamic filtering: When query filters depend on user inputs.
  • Conditional logic: When different query logic is executed based on conditions.
  • Table or column selection: When table names or column names are dynamic.
  • Performance optimization: In certain cases, dynamic SQL can be used to bypass fixed query structures, resulting in more optimized plans.

Dynamic SQL Syntax

There are two primary ways to execute dynamic SQL in MS SQL:

  1. Using EXEC The EXEC command is used to execute a string as a SQL query.

Example: Building a simple dynamic SQL query to get customers based on their city.

DECLARE @City NVARCHAR(50) = 'New York';
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = 'SELECT * FROM Customers WHERE City = ''' + @City + '''';
EXEC(@SQL);

In this example, the @SQL string is dynamically built, and the EXEC command runs the SQL statement.

  1. Using sp_executesql The sp_executesql system stored procedure is preferred for dynamic SQL when you need parameterization. It allows you to pass parameters into the query, reducing the risk of SQL injection and making the query more secure.

Example: A dynamic SQL query to fetch customers based on a city, using sp_executesql for parameterized execution.

DECLARE @SQL NVARCHAR(MAX);
DECLARE @City NVARCHAR(50) = 'New York';

SET @SQL = 'SELECT * FROM Customers WHERE City = @City';
EXEC sp_executesql @SQL, N'@City NVARCHAR(50)', @City = @City;

Here, the SQL query is parameterized, which protects it from SQL injection attacks.

Dynamic SQL with Conditional Filters

Dynamic SQL is especially powerful when you need to apply different filters based on user input or conditions.

Example: A dynamic query that includes conditions based on user input for city and customer type.

DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM Customers WHERE 1=1';
DECLARE @City NVARCHAR(50) = 'Los Angeles';
DECLARE @CustomerType NVARCHAR(50) = 'Premium';

IF @City IS NOT NULL
    SET @SQL = @SQL + ' AND City = @City';

IF @CustomerType IS NOT NULL
    SET @SQL = @SQL + ' AND CustomerType = @CustomerType';

EXEC sp_executesql @SQL, N'@City NVARCHAR(50), @CustomerType NVARCHAR(50)', 
                   @City = @City, @CustomerType = @CustomerType;

In this example, the query is constructed piece by piece, depending on which filters are applied. This flexibility allows different combinations of filters without hardcoding multiple query versions.

Handling Dynamic Table Names

Dynamic SQL allows for flexibility not only in conditions but also in selecting tables and columns dynamically.

Example: Fetch data from different customer tables based on a dynamic table name.

DECLARE @TableName NVARCHAR(50) = 'Customers_2023';
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = 'SELECT * FROM ' + @TableName;
EXEC(@SQL);

Here, the table name is provided dynamically at runtime, allowing the query to pull from different tables.

Security Considerations

Dynamic SQL can be vulnerable to SQL injection attacks if not handled carefully. To mitigate these risks:

  • Always use parameterized queries with sp_executesql when possible.
  • Validate and sanitize user inputs.
  • Avoid directly concatenating untrusted inputs into dynamic SQL queries.

SQL Injection Example (What to Avoid):

DECLARE @CustomerID NVARCHAR(50) = '1 OR 1=1';
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = 'SELECT * FROM Customers WHERE CustomerID = ' + @CustomerID;
EXEC(@SQL); -- This will execute with all rows due to the injection

Mitigating with Parameterization:

DECLARE @CustomerID NVARCHAR(50) = '1 OR 1=1';
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = 'SELECT * FROM Customers WHERE CustomerID = @CustomerID';
EXEC sp_executesql @SQL, N'@CustomerID NVARCHAR(50)', @CustomerID = @CustomerID;

By using sp_executesql, the input is treated as a parameter rather than part of the SQL code, preventing SQL injection.

Dynamic SQL and Performance

While dynamic SQL offers flexibility, it can also affect performance:

  • Execution Plan Caching: Each dynamically generated query can have a different execution plan, leading to frequent recompilations. Parameterized dynamic SQL (sp_executesql) helps reduce this by allowing plan reuse.
  • Complexity: Overly complex dynamic SQL can be hard to debug and optimize, so use it judiciously.

Example: Let’s consider a customer reporting system where a manager wants to generate custom reports for customer data, filtered by various conditions.

Scenario: The manager wants to view data filtered by multiple criteria, including city, purchase amount, and membership status.

DECLARE @City NVARCHAR(50) = 'Chicago';
DECLARE @MinPurchaseAmount DECIMAL(10, 2) = 100.00;
DECLARE @MembershipStatus NVARCHAR(50) = 'Active';
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = 'SELECT CustomerName, City, TotalSpent, MembershipStatus FROM Customers WHERE 1=1';

IF @City IS NOT NULL
    SET @SQL = @SQL + ' AND City = @City';

IF @MinPurchaseAmount IS NOT NULL
    SET @SQL = @SQL + ' AND TotalSpent >= @MinPurchaseAmount';

IF @MembershipStatus IS NOT NULL
    SET @SQL = @SQL + ' AND MembershipStatus = @MembershipStatus';

EXEC sp_executesql @SQL, 
    N'@City NVARCHAR(50), @MinPurchaseAmount DECIMAL(10,2), @MembershipStatus NVARCHAR(50)', 
    @City = @City, @MinPurchaseAmount = @MinPurchaseAmount, @MembershipStatus = @MembershipStatus;

This example dynamically builds a query based on the inputs provided, allowing flexibility in the reports generated by the customer management system.

Conclusion

Dynamic SQL in MS SQL is a powerful tool for building flexible and adaptable queries at runtime. It’s especially useful in scenarios where query logic, filters, or table names can change based on user input or business requirements. However, with this flexibility comes responsibility — careful parameterization and validation are crucial to prevent SQL injection attacks and ensure optimal performance.

Asked in Interview

  • What is Dynamic SQL, and how is it different from static SQL?
  • In what scenarios would you use Dynamic SQL?
  • How do you write and execute Dynamic SQL in SQL Server?
  • What are the security risks associated with Dynamic SQL, and how can you mitigate them?
  • What performance issues can arise with Dynamic SQL, and how can they be addressed?
  • How would you dynamically build a WHERE clause in SQL based on multiple optional conditions?
  • Suppose you need to dynamically query different tables depending on user input. How would you handle this using Dynamic SQL?