sp_executesql_sp_executesql存储过程简介和示例

sp_executesql

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.

sp_executesql是SQL Server中的内置存储过程，可用于执行动态构造SQL语句或批处理。 执行动态构造SQL批处理是一种有时可以克服SQL编程中不同问题的技术。 例如，当我们要确定报告中显示的列时，此过程可能是我们的解决方案。 从最简单的意义上讲，此过程采用动态构造SQL批处理和其他参数，然后在运行时中执行它，最后返回结果。

• Note: In this article’s examples, the sample 注意：在本文的示例中，将使用示例AdventureWorks database will be used.AdventureWorks数据库。

sp_executesql语法 (sp_executesql syntax)

The following code describes the syntax:

以下代码描述了语法：

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.

@stmt参数用于指定动态生成SQL语句或批处理。 此参数的数据类型必须是Unicode字符串，因此，我们必须为直接文本用法添加N前缀，或者必须使用nvarchar或nchar数据类型的变量。

@parameternameN_datatype defines the parameter’s name and data type that has been used in the dynamically constructed SQL statements.

@parameternameN_datatype定义参数名称和已在 动态构造SQL语句。

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.

借助@ parameternameN =’ValueN’表达式， 我们可以为放置在SQL语句中的已定义参数分配一个值。 在本文的以下各节中，我们将通过简单到困难的示例探索用法细节。

sp_executesql示例 (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:

该示例的目的是从“ 个人”表中检索数据，该表以AdventureWorks数据库的相同模式参与：

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:

动态构造SQL语句将分配给@SqlStatment变量。 @ColName变量用于指定我们要在查询结果集中显示的列名。 最后，我们将使用@PerType参数过滤Person表数据。 此参数数据类型将为nchar（2），并过滤其Persontype列表达式等于“ EM”的数据。 作为最后一步，我们将执行查询并获得结果：

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:

由于分配的值，查询的结果集仅显示FirstName，MiddleName和LastName列 @ColNames变量。 同时，我们可以使用此参数调整显示列的名称。 例如，以下示例将仅显示“ 名字”列：

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'

使用输出参数获取sp_executesql结果 (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:

sp_executesql提供返回动态构造SQL语句或批处理的执行结果。 OUTPUT参数在解决这种情况方面起着关键作用。 在此示例中，我们将计算PersonPhone表的行号，然后将返回值设置为带有OUTPUT参数的变量。 这种用法的窍门是将@RowNumber参数指示为OUTPUT参数，然后我们将此内部参数值分配给@Result参数：

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语句 (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:

EXEC语句是执行动态SQL语句的另一种选择。 例如，我们可以通过EXEC语句执行以下动态构造SQL语句：

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.

在前面的示例中，我们使用EXEC语句执行了动态构造的查询，但是我们需要考虑一点。 我们无法参数化EXEC语句，这是它的主要缺点。

sp_executesql has some advantages comparing to the EXEC statement. Now, let’s take a glance at these:

与EXEC语句相比，sp_executesql具有一些优点。 现在，让我们看一下这些：

• sp_executesql has the ability to reuse the cached query plans

  sp_executesql能够重用缓存的查询计划

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.

在SQL Server中执行的每个查询在执行之前都会被编译。 该查询编译过程生成一个称为查询计划的输出。 但是，此查询编译过程有时可能非常昂贵。 因此，SQL Server希望对相同的查询尽可能重复使用缓存的查询计划，以降低查询的编译成本。 现在，我们将证明这个想法。

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:

首先，我们将使用FREEPROCCACHE清除所有缓存的计划。 但是，请勿在生产环境中执行此命令，因为它可能会损坏SQL Server的性能：

DBCC FREEPROCCACHE

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

在此步骤中，我们将使用随机参数执行以下查询3次。

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:

现在，我们将在sys.dm_exec_cached_plans中检查生成的查询计划：

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:

现在，我们将为EXEC语句重复一个类似的测试场景：

DBCC FREEPROCCACHE

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

在此步骤中，我们将使用EXEC语句对随机参数执行3次动态构造的查询：

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:

现在，我们将重新检查sys.dm_exec_cached_plans视图，以查看创建了多少个查询计划：

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.

结果，sp_executesql在第一次执行查询时生成了一个查询计划，然后它一次又一次使用相同的查询计划。 尽管如此，EXEC语句还是为每次查询执行创建了新的查询计划。 这种使用类型可能会消耗SQL Server资源，并且可能是由于性能问题引起的。

• Note: sp_executesql allows for generating parameterized dynamic queries. So that it is more secure to 注意： sp_executesql允许生成参数化的动态查询。 这样可以更安全地防止SQL injection attacks. EXEC statement is more vulnerable in terms of SQL injections. SQL注入攻击。 就SQL注入而言，EXEC语句更容易受到攻击。

结论 (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.

在本文中，我们探讨了sp_executesql过程的详细信息，并学习了使用方法。 此过程对于解决动态查询问题非常有用，但是，当我们决定在SQL Server中使用动态查询时，我们必须考虑SQL注入问题。

翻译自: https://www.sqlshack.com/introduction-to-sp_executesql-stored-procedure-with-examples/

sp_executesql