The sp_executesql is a built-in stored procedure in SQL Server that enables to execute of the dynamically constructed SQL statements or batches. Executing the dynamically constructed SQL batches is a technique used to overcome different issues in SQL programming sometimes. For example, when we want to determine the displayed columns in our reports, this procedure might be a solution option for us. In the simplest sense, this procedure takes a dynamically constructed SQL batch and other parameters, then execute it in the runtime and, finally, it returns the result.
- Note: In this article’s examples, the sample AdventureWorks database will be used.
sp_executesql syntax
The following code describes the syntax:
|
1 2 |
sp_executesql @stmt ,N'@parametername1_datatype,@parametername2_datatype,@parameternameN_datatype' ,@parametername1='Value1' ,@parametername2='Value2',@parameternameN='ValueN' |
@stmt parameter is used to specify dynamically generated SQL statement or batch. The data type of this parameter must be Unicode strings, for this reason, we have to add N prefix for the direct text usage or have to use nvarchar or nchar data typed variables.
@parameternameN_datatype defines the parameter’s name and data type that has been used in the dynamically constructed SQL statements.
With the help of the @parameternameN=’ValueN’ expression, we can assign a value to the defined parameters which are placed in the SQL statement. In the following sections of the article, we will explore the usage details with examples from easy to difficult.
sp_executesql example
The purpose of this example is, retrieving data from the Person table which is taking part under the same schema on the AdventureWorks database:
The dynamically constructed SQL statement will be assigned to the @SqlStatment variable. The @ColName variable is used to specify the column names, that we want to display in the result set of the query. As a last, we will filter the Person table data with the @PerType parameter. This parameter data type will be nchar(2) and filter the data whose Persontype column expressions equal to “EM”. As the last step, we will execute the query and achieve the result:
|
1 2 3 4 5 6 7 |
DECLARE @SqlStatment AS NVARCHAR(1000) DECLARE @ColNames AS NVARCHAR(100) SET @ColNames = N'FirstName , MiddleName , LastName'; SET @SqlStatment = 'SELECT ' + @ColNames + ' FROM Person.Person WHERE Persontype=@PerType' EXECUTE sp_executesql @SqlStatment , N'@PerType nchar(2)',@PerType='EM' |
The result set of the query shows only FirstName, MiddleName and LastName columns because of the assigned value of the @ColNames variable. At the same time, we can adjust the displaying column names with this parameter. For example, the following example will be displayed only FirstName column:
|
1 2 3 4 5 6 7 |
DECLARE @SqlStatment AS NVARCHAR(1000) DECLARE @ColNames AS NVARCHAR(100) SET @ColNames = N'FirstName'; SET @SqlStatment = 'SELECT ' + @ColNames + ' FROM Person.Person WHERE Persontype=@PerType' EXECUTE sp_executesql @SqlStatment , N'@PerType nchar(2)',@PerType='EM' |
Getting sp_executesql result with output parameter
sp_executesql provides to return execution result of the dynamically constructed SQL statement or batch. The OUTPUT parameter plays a key role to resolve this case. In this example, we will count the row number of the PersonPhone table and then we will set the return value to a variable with the OUTPUT parameter. The trick of this usage is to indicate the @RowNumber parameter as an OUTPUT parameter and then we assigned this internal parameter value to the @Result parameter:
|
1 2 3 4 5 6 7 8 9 |
DECLARE @SqlStatment AS NVARCHAR(1000) DECLARE @PhoneIdType AS INT DECLARE @Result AS INT SET @SqlStatment='SELECT @RowNumber= COUNT(PhoneNumber) from Person.PersonPhone WHERE PhoneNumberTypeID=@PhoneType' SET @PhoneIdType=1 EXEC sp_executesql @SqlStatment , N'@PhoneType INT,@RowNumber INT OUTPUT' , @PhoneType=@PhoneIdType ,@RowNumber=@Result OUTPUT SELECT @Result AS [TableRowNumber] |
sp_executesql vs EXEC statement
The EXEC statement is another option to execute the dynamic SQL statements. For example, we can execute the following dynamically constructed SQL statement through the EXEC statement:
|
1 2 3 4 5 6 |
DECLARE @SqlStatment AS NVARCHAR(1000) DECLARE @ColNames AS NVARCHAR(100) DECLARE @Persontype AS NVARCHAR(2)= 'EM' SET @ColNames = N'FirstName , MiddleName , LastName'; SET @SqlStatment = 'SELECT ' + @ColNames + ' FROM Person.Person WHERE Persontype= ''' + @Persontype + '''' EXEC(@SqlStatment) |
In the previous example, we executed the dynamically constructed query with the EXEC statement but we need to take account one point about it. We could not parametrize the EXEC statement and this is the main drawback of it.
sp_executesql has some advantages comparing to the EXEC statement. Now, let’s take a glance at these:
- sp_executesql has the ability to reuse the cached query plans
Each query executed in SQL Server is compiled before it is executed. This query compilation process generates an output that is called the query plan. However, this query compilation process might be very expensive sometimes. For this reason, SQL Server wishes to reuse the cached query plans as possible as for the same queries in order to degrade the query compilation costs. Now, we will prove this idea.
At first, we will clear all the cached plans with FREEPROCCACHE. However, do not execute this command in the production environment because it could be damage to the performance of the SQL Server:
|
1 |
DBCC FREEPROCCACHE |
In this step, we will execute the following query 3 times with the random parameters.
|
1 2 3 4 5 6 7 8 |
DECLARE @SqlStatment AS NVARCHAR(1000) DECLARE @PhoneIdType AS INT DECLARE @Result AS INT SET @SqlStatment='SELECT @RowNumber= COUNT(PhoneNumber) from Person.PersonPhone WHERE PhoneNumberTypeID=@PhoneType' SET @PhoneIdType=ROUND(((20 - 1) * RAND() + 1), 0) EXEC sp_executesql @SqlStatment , N'@PhoneType INT,@RowNumber INT OUTPUT' , @PhoneType=@PhoneIdType ,@RowNumber=@Result OUTPUT GO 3 |
Now we will check out the generated query plans in the sys.dm_exec_cached_plans:
|
1 2 3 4 5 6 7 8 |
SELECT SText.text, * FROM sys.dm_exec_cached_plans CachedPlans CROSS APPLY sys.dm_exec_sql_text (CachedPlans.plan_handle) SText WHERE SText.text LIKE '%SELECT @RowNumber= COUNT(PhoneNumber) from Person.PersonPhone WHERE PhoneNumberTypeID=@PhoneType%' AND SText.text NOT LIKE '%sys.dm_exec_cached_plans%'; |
Now, we will repeat a similar test scenario for the EXEC statement:
|
1 |
DBCC FREEPROCCACHE |
In this step, we will execute the dynamically constructed query 3 times for the random parameters with the EXEC statement:
|
1 2 3 4 5 6 |
DECLARE @Param AS INT=ROUND(((20 - 1) * RAND() + 1), 0) DECLARE @St AS NVARCHAR(1000) ='SELECT COUNT(PhoneNumber) AS Result from Person.PersonPhone WHERE PhoneNumberTypeID = ' SET @St =CONCAT(@St,@Param) PRINT @St EXEC(@St) GO 3 |
Now, we will re-check sys.dm_exec_cached_plans view to see how many query plans were created:
|
1 2 3 4 5 6 7 8 |
SELECT SText.text, * FROM sys.dm_exec_cached_plans CachedPlans CROSS APPLY sys.dm_exec_sql_text (CachedPlans.plan_handle) SText WHERE SText.text LIKE '%SELECT COUNT(PhoneNumber) AS Result from Person.PersonPhone WHERE PhoneNumberTypeID%' AND SText.text NOT LIKE '%sys.dm_exec_cached_plans%'; |
As a result, sp_executesql generated a one query plan in the first execution of the query and then it used the same query plan again and again. In spite of that, the EXEC statement created new query plans for each query execution. This type of usage could consume SQL Server resources and could be caused by performance issues.
- Note: sp_executesql allows for generating parameterized dynamic queries. So that it is more secure to SQL injection attacks. EXEC statement is more vulnerable in terms of SQL injections.
Conclusion
In this article, we explored the sp_executesql procedure details and learned the usage methods. This procedure is very useful to resolve the dynamic query issues however, we have to consider the SQL injection issues when we decide to use dynamic queries in SQL Server.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023