When working with SQL Server, there may be times when you need to execute T-SQL code that will be different at runtime compared to the time it was written. This is known as Dynamic TSQL, where the code determines the actual T-SQL to be executed. In this article, we will explore how to write Dynamic TSQL using the sp_executesql function and discuss its usefulness.

Before we proceed, it is important to note that using sp_executesql without proper checks and safeguards can be risky. It is crucial to understand the potential risks, such as SQL Injection, and take necessary precautions to protect your database.

The sp_executesql function allows you to pass variable values separately from the defined T-SQL statement. These variables need to be declared as part of a parameter definition, which is then passed as a single parameter to the sp_executesql function. Let’s look at a couple of examples:

Example 1:

DECLARE @TSQLDefinition NVARCHAR(100)
DECLARE @SalesQuotaVar DECIMAL(6, 2)
DECLARE @TSQLVariableDefinitions NVARCHAR(500)

SET @TSQLVariableDefinitions = N'@SalesQuotaVal decimal(6,2)'
SET @SalesQuotaVar = 200.00
SET @TSQLDefinition = 'SELECT TOP 10 * FROM Sales.SalesPerson WHERE SalesQuota > @SalesQuotaVal'

EXEC sys.sp_executesql @TSQLDefinition, @TSQLVariableDefinitions, @SalesQuotaVal = @SalesQuotaVar;

Example 2:

DECLARE @TSQLDefinition NVARCHAR(500)
DECLARE @TerritoryVar INT
DECLARE @SalesQuotaVar DECIMAL(6, 2)
DECLARE @TSQLParameterDefinitions NVARCHAR(500)

SET @TSQLParameterDefinitions = N'@SalesQuotaVal decimal(6,2), @TerritoryID INT'
SET @SalesQuotaVar = 200.00
SET @TerritoryVar = 969
SET @TSQLDefinition = 'SELECT TOP 10 * FROM Sales.SalesPerson WHERE SalesQuota > @SalesQuotaVal and TerritoryID = @TerritoryID'

EXEC sys.sp_executesql @TSQLDefinition, @TSQLParameterDefinitions, @SalesQuotaVal = @SalesQuotaVar, @TerritoryID = @TerritoryVar;

In these examples, we define the T-SQL statement to be executed and the corresponding parameter definitions. The values for these parameters are then passed when executing the sp_executesql function. Unused parameters do not cause errors, but if a required parameter is not defined, an error will occur.

Another way to use sp_executesql is by building the T-SQL statement within a loop of ‘outer’ T-SQL. This allows you to dynamically generate and execute T-SQL statements for multiple tables, as shown in the following example:

DECLARE @TSQLDefinition NVARCHAR(500)
DECLARE @TableNameVar NVARCHAR(100)
DECLARE @TableList TABLE (TableName NVARCHAR(100))

/* Collect table names into a table variable */
INSERT @TableList (TableName)
SELECT Name FROM sys.tables WHERE type_desc = 'USER_TABLE'

WHILE EXISTS (SELECT * FROM @TableList)
BEGIN
    /* Build the TSQL statement for one table */
    SELECT TOP 1 @TableNameVar = tl.TableName
    FROM @TableList AS tl
    SET @TSQLDefinition = 'SELECT COUNT(*) AS ' + @TableNameVar + ' FROM ' + @TableNameVar

    /* Execute the TSQL */
    EXEC sys.sp_executesql @TSQLDefinition

    /* Remove the tablename from the list of tables */
    DELETE FROM @TableList WHERE TableName = @TableNameVar
END

This example demonstrates how you can dynamically generate T-SQL statements for each table in a database and execute them using sp_executesql.

By utilizing sp_executesql, you can leverage the power of dynamic TSQL in SQL Server. However, it is crucial to be aware of the potential risks and take necessary precautions to protect your database from SQL Injection attacks. Use this feature carefully and ensure all safeguards are in place.

References:

• sp_executesql – Microsoft Docs
• SQL Injection – Microsoft Docs