sp_executesql_sp_executesql存储过程简介和示例-linux运维-PHP中文网

大家好，我们又见面了，我是你们的朋友全栈君。

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 表中检索数据：

sp_executesql_sp_executesql存储过程简介和示例

动态构建的 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'

登录后复制

sp_executesql_sp_executesql存储过程简介和示例

由于分配的值，查询的结果集仅显示 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_sp_executesql存储过程简介和示例

使用输出参数获取 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_sp_executesql存储过程简介和示例

sp_executesql vs EXEC 语句 (sp_executesql vs 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)

登录后复制

sp_executesql_sp_executesql存储过程简介和示例

在前面的示例中，我们使用 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%';

登录后复制

sp_executesql_sp_executesql存储过程简介和示例

现在，我们将为 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_sp_executesql存储过程简介和示例

结果，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

以上就是sp_executesql_sp_executesql存储过程简介和示例的详细内容，更多请关注php中文网其它相关文章！