Every once in a while you find yourself working with SQL Server and you need to execute a piece of T-SQL that will be  different at the time of execution from the time that the code is being written. This is known as Dynamic TSQL; the code that you are writing will determine what actual T-SQL will be executed at run-time.

We will have a quick look at how to write Dynamic TSQL using the sp_executesql function and make use of this feature. For the purposes of this article we will be working with a copy of the AdventureWorks database but any database will do while you test and learn about this function.

In its most basic form sp_executesql take a string value that is assumed to be a valid TSQL statement and executes it.

USE AdventureWorks
GO
DECLARE @TSQLDefinition NVARCHAR(100)
SET @TSQLDefinition = 'SELECT TOP 10 * FROM Sales.SalesPerson';
EXEC sys.sp_executesql @TSQLDefinition;

Before we go much further I need to point out that this is clearly like taking a suitcase from a random stranger at an airport and trying to get through customs – it can lead you to an awful lot of trouble very quickly. Having code that simply executes T-SQL without the relevant checks and safeguards is reckless. While learning about sp_executesql you also need to learn about SQL Injection and the ways it can destroy your database¹. There are details on SQL Injection in the references section on this page. Read the rest of this with this warning in mind – sp_executesql can be incredibly useful at times but use it carefully in full understanding of what can go wrong, what risks you are taking on and with all safeguards in place.

sp_executesql provides the ability to pass in a variable value or values that is separate from the defined T-SQL. This variable, or variables, need to be declared as part of a parameter definition that is passed in as a single parameter to the sp_executesql function.

In this example the T-SQL being executed uses a parameter called @SalesQuotaVal. This parameter is defined in the @TSQLVariableDefinitions variable and gets it's value assigned by setting it to be the same value as the @SalesQuotaVar variable from the outer T-SQL.

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;
GO

In this second example the @TSQLVariableDefinitions variable has two variables defined which are used in the @TSQLDefinition when executed. 

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;
GO

If a parameter is defined but unused then there is no error, if a parameter is defined but not set a value then the @TSQLDefinition will be executed with NULL being used for any comparison or evaluation. If a parameter is required within @TSQLDefinition but is not defined in @TSQLParameterDefinition then an error occurs.

Another way to use sp_executesql is to build the TSQL you want to execute in a loop of 'outer' T-SQL as below

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 we want executed 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
GO

Hopefully this shows you a number of ways to use sp_executesql to your advantage.

References

1. Full details of sp_executesql can be found at http://technet.microsoft.com/en-us/library/ms188001.aspx
2. Books Online details concerning SQL Injection can be found at http://technet.microsoft.com/en-us/library/ms161953(v=sql.105).aspx

¹ – and your career.