Introduction

Dynamic SQL is a powerful technique in SQL Server that allows you to construct and execute SQL statements dynamically at runtime. It provides flexibility and enables you to create queries based on user input or variable conditions. In this article, we’ll explore practical examples and applications of dynamic SQL in T-SQL.

Building Dynamic Queries

One common use case for dynamic SQL is building queries based on user input. For example, let’s say we have a stored procedure that accepts a table name as a parameter and returns the data from that table:

CREATE PROCEDURE GetTableData
    @TableName NVARCHAR(100)
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX);
    SET @SQL = 'SELECT * FROM ' + @TableName;
    EXEC sp_executesql @SQL;
END

In this code, we construct the SQL statement dynamically by concatenating the table name with the SELECT statement. The sp_executesql system stored procedure is then used to execute the dynamic SQL.

Executing Parameterized Queries

When using dynamic SQL, it’s crucial to protect against SQL injection attacks. One way to achieve this is by using parameterized queries. Here’s an example:

DECLARE @SQL NVARCHAR(MAX);
DECLARE @ProductID INT = 1;

SET @SQL = 'SELECT ProductName, UnitPrice FROM Products WHERE ProductID = @ProductID';

EXEC sp_executesql @SQL, N'@ProductID INT', @ProductID;

In this case, we define the parameter @ProductID separately and pass it to sp_executesql along with the SQL statement. This approach ensures that the parameter value is treated as data and not as part of the SQL code, preventing injection attacks.

“SQL injection attacks occur when malicious SQL statements are inserted into application queries, allowing attackers to manipulate the database. By exploiting vulnerabilities in user input validation, attackers can bypass security measures, modify data, and gain unauthorized access to sensitive information, compromising the integrity and security of the system.“

Generating Dynamic SQL with Conditions

Dynamic SQL also allows you to generate queries based on variable conditions. Consider the following example:

DECLARE @SQL NVARCHAR(MAX);
DECLARE @CategoryID INT = 1;
DECLARE @SupplierID INT = NULL;

SET @SQL = 'SELECT ProductName, UnitPrice FROM Products WHERE CategoryID = @CategoryID';

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

EXEC sp_executesql @SQL, N'@CategoryID INT, @SupplierID INT', @CategoryID, @SupplierID;

Here, we start with a base query and conditionally append an additional WHERE clause if the @SupplierID parameter is not NULL. This enables us to dynamically adjust the query based on the provided parameter values.

Conclusion

Dynamic SQL is a versatile tool in SQL Server that allows you to create flexible and adaptable queries. By leveraging dynamic SQL, you can build queries based on user input, execute parameterized queries, and generate SQL statements dynamically based on conditions. However, it’s essential to exercise caution and follow best practices to prevent SQL injection vulnerabilities when using dynamic SQL.

To learn more about dynamic SQL in SQL Server, visit the official Microsoft documentation: Dynamic SQL