Esat Erkec

Introduction to the sp_executesql stored procedure with examples

January 9, 2020 by

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:

@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:

Person table in 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:

sp_executesql example result for 3 column

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:

sp_executesql example result for one column

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:

Using the output parameter in sp_executesql

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:

Using EXEC statement to execute dynamic queries.

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:

In this step, we will execute the following query 3 times with the random parameters.

Now we will check out the generated query plans in the sys.dm_exec_cached_plans:

sp_executesql execution plan reusing mechanism

Now, we will repeat a similar test scenario for the EXEC statement:

In this step, we will execute the dynamically constructed query 3 times for the random parameters with the EXEC statement:

Now, we will re-check sys.dm_exec_cached_plans view to see how many query plans were created:

EXEC statement execution plan details

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.

Esat Erkec
7,229 Views