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 database1. 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.
- Full details of sp_executesql can be found at http://technet.microsoft.com/en-us/library/ms188001.aspx
- Books Online details concerning SQL Injection can be found at http://technet.microsoft.com/en-us/library/ms161953(v=sql.105).aspx
1 – and your career.