Build Dynamic SQL Queries in SQL Server

Build Dynamic SQL Queries in SQL Server

Introduction:
Dynamic SQL allows you to construct SQL statements dynamically at runtime. It allows you to create flexible queries that can adapt to varying conditions or requirements. However, dynamic SQL should be used with caution to prevent SQL injection vulnerabilities.

Benefits of Dynamic SQL:

• Flexibility in query construction.
• Dynamic filtering and sorting.
• Conditionally adding or removing columns and tables.

1. Dynamic SQL in T-SQL:

Dynamic SQL in SQL Server can be implemented using the EXEC or sp_executesql statement. The EXEC statement is used for simple dynamic queries, while sp_executesql is preferred for its parameterization capabilities, which enhance security.

Example:
Let's create a dynamic SQL query that retrieves data from a table based on a provided column name and a filter condition.

sql
DECLARE @columnName NVARCHAR(50) = 'ProductName';
DECLARE @filterCondition NVARCHAR(100) = 'Shoes';

DECLARE @sqlQuery NVARCHAR(MAX);

SET @sqlQuery = N'SELECT ' + QUOTENAME(@columnName) + ' FROM Products WHERE ' + QUOTENAME(@columnName) + ' = @filter';

EXEC sp_executesql @sqlQuery, N'@filter NVARCHAR(100)', @filter = @filterCondition;

Explanation:

• We declare two variables @columnName and @filterCondition, which will hold the column name and filter value, respectively.
• QUOTENAME function is used to ensure that the column name is properly quoted to prevent SQL injection.
• The dynamic query is built using the provided column name and filter condition.
• sp_executesql executes the dynamic SQL statement with parameterization, ensuring safety against SQL injection.

2. Using Dynamic SQL for Dynamic Sorting:

Dynamic SQL is also useful for dynamically generating sorting conditions in a query.

Example:
Let's create a dynamic SQL query that retrieves data from a table and dynamically sorts it based on the provided column name and sort order.

sql
DECLARE @sortColumn NVARCHAR(50) = 'ProductName';
DECLARE @sortOrder NVARCHAR(10) = 'DESC';

DECLARE @sqlQuery NVARCHAR(MAX);

SET @sqlQuery = N'SELECT * FROM Products ORDER BY ' + QUOTENAME(@sortColumn) + ' ' + @sortOrder;

EXEC sp_executesql @sqlQuery;

Explanation:

• We declare two variables @sortColumn and @sortOrder, which will hold the column name and sort order, respectively.
• QUOTENAME function is used to ensure that the column name is properly quoted to prevent SQL injection.
• The dynamic query is built using the provided column name and sort order.
• sp_executesql executes the dynamic SQL statement to retrieve the sorted data.

Important Note:
When using dynamic SQL, be cautious of SQL injection vulnerabilities. Always validate and sanitize user inputs before constructing dynamic queries.

Conclusion:
Dynamic SQL in SQL Server offers flexibility and adaptability in query construction. It enables you to create powerful and flexible queries, but it should be used responsibly to ensure the security of your database and prevent potential exploits.