EXECUTE versus sp_executesql

There are times when an application must build a T-SQL statement dynamically before executing it on the server. In order to execute a dynamically created T-SQL statement, we can use either the EXECUTE command or the sp_executesql stored procedure. The sp_executesql procedure is the preferred method for executing dynamic T-SQL because it allows us to add parameter markers and thus increases the likelihood that the SQL Database Engine will be able to reuse the plan and avoid costly query compilations.

Here’s an example script from the AdventureWorks database that builds a dynamic T-SQL statement and executes it via the EXECUTE command:

DECLARE @sql nvarchar(MAX), @JobTitle nvarchar(50) = N'Sales Representative';
SET @sql = 'SELECT e.BusinessEntityID, p.FirstName, p.LastName
FROM HumanResources.Employee e
INNER JOIN Person.Person p ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.JobTitle = N''' + @JobTitle + '''';
EXECUTE (@sql);

Notice that there is a variable for the JobTitle column, but the EXECUTE command does not allow parameters, so this variable is appended to the T-SQL string in order to include it in the resulting query. We can reuse the same script by changing 'Sales Representative' to 'Accountant' and re-running it, but because the resulting query is not parameterized, the SQL Database Engine will have to compile and cache the query again. We can verify this by examining the sys.dm_exec_query_stats dynamic management view (DMV). Recall from the Query plan properties of interest section of Chapter 3, Exploring Query Execution Plans, that there is a property called QueryHash that contains a value that can identify a query in the cache and will return all the queries that are syntactically equivalent but have different query strings for some reason:

SELECT st.text, qs.sql_handle, qs.execution_count
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE qs.query_hash = 0x3A17ADF596F7D5C9;

This query returns the following results:

Figure 5.40 – Result set showing different SQL handles for the same query hash

We can see that there are two different queries here, one for each of the different JobTitle values, and each has a single execution. Each execution of the script resulted in a separate compilation and a separate cached query plan.

Note

We will discuss sys.dm_exec_query_stats as well as other dynamic management views in more detail in Chapter 7, Building Diagnostic Queries Using DMVs and DMFs.

Let’s see how we can rewrite this script using sp_executesql instead:

DECLARE @sql nvarchar(MAX), @JobTitle nvarchar(50) = N'Sales Representative';
SET @sql = 'SELECT e.BusinessEntityID, p.FirstName, p.LastName
FROM HumanResources.Employee e
INNER JOIN Person.Person p ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.JobTitle = @p1';
EXEC sp_executesql @sql, N'@p1 nvarchar(50)', @JobTitle;

Notice that in this case, we can use the @JobTitle variable as a parameter in the query. If we change the value of @JobTitle to 'Accountant' and run the query again, the SQL Database Engine can reuse the existing execution plan from the cache. We can verify this by running the same query against sys.dm_exec_query_stats with the QueryHash from this new query. This time, the results are different:

Figure 5.41 – Result set showing the same SQL handle for the same query hash and two executions

Notice that the query in the cache has a parameter marker, and the execution count is 2, indicating that the query plan has been reused.

Whenever our application requires dynamic T-SQL for any reason, using the sp_executesql procedure rather than the EXECUTE command is generally more efficient because it will increase the likelihood that the SQL Database Engine can reuse the query plan. Also recall that in the The importance of parameters section in the Understanding Query Processing chapter, we mentioned that parameters and the use of sp_executesql can also help prevent SQL injection attacks, so it is more secure than using EXECUTE. For these reasons, sp_executesql is the recommended method for executing dynamic T-SQL. Now that we know the proper way to execute dynamic T-SQL, let’s look at another common programming problem – composable logic – and how it might actually perform better if written as dynamic T-SQL.