大家好，我们又见面了，我是你们的朋友全栈君。
sp_executesql
sp_executesql 是 SQL Server 中的一个内置存储过程，用于执行动态构建的 SQL 语句或批处理。执行动态构建的 SQL 批处理是一种技术，可以在 SQL 编程中解决某些问题。例如，当我们需要确定报告中显示的列时，这个过程可能是一个可行的解决方案。从最简单的意义上讲，这个过程接受一个动态构建的 SQL 批处理和其他参数，然后在运行时执行它，最后返回结果。
sp_executesql 是 SQL Server 中的内置存储过程，适用于执行动态构建的 SQL 语句或批处理。通过执行动态构建的 SQL 批处理，这种技术有时能解决 SQL 编程中的不同问题。例如，若我们希望确定报告中要显示的列，此过程可能是一个可行的选择。从最基本的层面上讲，此过程接收动态构建的 SQL 批处理和其他参数，然后在运行时执行它，并最终返回结果。
注意：在本文的示例中，将使用 AdventureWorks 数据库。
sp_executesql 语法 (sp_executesql syntax)
以下代码描述了语法：
sp_executesql @stmt ,N'@parametername1_datatype,@parametername2_datatype,@parameternameN_datatype',@parametername1='Value1' ,@parametername2='Value2',@parameternameN='ValueN'
@stmt 参数用于指定动态生成的 SQL 语句或批处理。此参数的数据类型必须是 Unicode 字符串，因此，我们必须为直接文本用法添加 N 前缀，或者必须使用 nvarchar 或 nchar 数据类型的变量。
@parameternameN_datatype 定义了在动态构建的 SQL 语句中使用的参数的名称和数据类型。
通过 @parameternameN='ValueN' 表达式，我们可以为 SQL 语句中放置的已定义参数分配一个值。在本文的以下各节中，我们将通过从简单到复杂的示例探索用法细节。
sp_executesql 示例 (sp_executesql example)
该示例的目的是从 AdventureWorks 数据库中的 Person 表中检索数据：
动态构建的 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'
由于分配的值，查询的结果集仅显示 FirstName、MiddleName 和 LastName 列 @ColNames 变量。同时，我们可以使用此参数调整显示列的名称。例如，以下示例将仅显示 FirstName 列：
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 提供了返回动态构建的 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)
EXEC 语句是执行动态 SQL 语句的另一种选择。例如，我们可以通过 EXEC 语句执行以下动态构建的 SQL 语句：
采用C#.NET,多层架构开发，后台采用大型MS SQL SERVER 数据库和存储过程,速度、性能更胜一筹。 前台功能介绍: 1、网页首页显示有精品推荐,商业机会分类列表,最新供求信息,网站动态,最新企业等; 2、商业机会栏目功能有：二级分类,已经带有详细分类的数据库,后台可以更改增加操作; 3、展厅展品栏目功能:二级分类,已经带有详细分类的数据库,后台可以更改增加操作,栏目分为分类显示展示的产
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)
在前面的示例中，我们使用 EXEC 语句执行了动态构建的查询，但是我们需要考虑一点。我们无法对 EXEC 语句进行参数化，这是它的主要缺点。
与 EXEC 语句相比，sp_executesql 具有一些优点。现在，让我们看一下这些：
sp_executesql 能够重用缓存的查询计划
在 SQL Server 中执行的每个查询在执行之前都会被编译。这个查询编译过程生成一个称为查询计划的输出。然而，这个查询编译过程有时可能非常昂贵。因此，SQL Server 希望对相同的查询尽可能重复使用缓存的查询计划，以降低查询的编译成本。现在，我们将证明这个想法。
首先，我们将使用 FREEPROCCACHE 清除所有缓存的计划。然而，请勿在生产环境中执行此命令，因为它可能会损害 SQL Server 的性能：
DBCC FREEPROCCACHE
在此步骤中，我们将使用随机参数执行以下查询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
现在，我们将在 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%';
现在，我们将为 EXEC 语句重复一个类似的测试场景：
DBCC FREEPROCCACHE
在此步骤中，我们将使用 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
现在，我们将重新检查 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%';
结果，sp_executesql 在第一次执行查询时生成了一个查询计划，然后它一次又一次地使用相同的查询计划。尽管如此，EXEC 语句为每次查询执行创建了新的查询计划。这种使用类型可能会消耗 SQL Server 资源，并可能导致性能问题。
注意：sp_executesql 允许生成参数化的动态查询。因此，它在防止 SQL 注入攻击方面更为安全。相比之下，EXEC 语句在 SQL 注入方面更容易受到攻击。
结论 (Conclusion)
在本文中，我们探讨了 sp_executesql 过程的详细信息，并学习了使用方法。这个过程对于解决动态查询问题非常有用，但是，当我们决定在 SQL Server 中使用动态查询时，我们必须考虑 SQL 注入问题。
sp_executesql
发布者：全栈程序员栈长，转载请注明出处：https://www.php.cn/link/51c9e6f87ce2a2bdc6e2be26ee89e825 原文链接：https://www.php.cn/link/c8377ad2a50fb65de28b11cfc628d75c
