sp_executesql 与 execute 的差异

最新推荐文章于 2016-12-22 10:58:45 发布
最新推荐文章于 2016-12-22 10:58:45 发布
阅读量1.1k 收藏
点赞数
分类专栏： 数据库(mssql) 文章标签： sql server database 数据库 存储 string 优化
 

建议您在执行字符串时，使用 sp_executesql 存储过程而不要使用 EXECUTE 语句。由于此存储过程支持参数替换，因此 sp_executesql 比 EXECUTE 的功能更多；由于 SQL Server 更可能重用 sp_executesql 生成的执行计划，因此 sp_executesql 比 EXECUTE 更有效。

当 sp_executesql 或 EXECUTE 语句执行字符串时，字符串将作为它的自包含批处理执行。SQL Server 会将字符串中的一个或多个 Transact-SQL 语句编译为独立于批处理（包含 sp_executesql 或 EXECUTE 语句）执行计划的执行计划。下列规则适用于自包含批处理：

  • 在执行 sp_executesql 或 EXECUTE 语句之前，不会将 sp_executesql 或 EXECUTE 字符串中的 Transact-SQL 语句编译到执行计划中。执行字符串之前，不会分析或检查其错误。执行时才对字符串中引用的名称进行解析。
  • 已执行的字符串中的 Transact-SQL 语句不能访问包含 sp_executesql 或 EXECUTE 语句的批处理中声明的任何变量。包含 sp_executesql 或 EXECUTE 语句的批处理不能访问已执行字符串中定义的变量或局部游标。
  • 如果已执行字符串包含一个更改数据库上下文的 USE 语句，则对数据库上下文所做的更改将仅持续到 sp_executesql 或 EXECUTE 语句运行结束。

运行下列两个批处理说明了这些方面：

  
/*Show not having access to variables from the calling batch. */
DECLARE @CharVariable CHAR(3);
SET @CharVariable = 'abc';
/* sp_executesql fails because @CharVariable has gone out of scope. */
EXECUTE sp_executesql N'PRINT @CharVariable';
GO

/* Show database context resetting after sp_executesql finishes. */
USE master;
GO
EXECUTE sp_executesql N'USE AdventureWorks;'
GO
/* This statement fails because the database context
   has now returned to master. */
SELECT * FROM Sales.Store;
GO

sp_executesql 支持替换 Transact-SQL 字符串中指定的任何参数值，但 EXECUTE 语句不支持。因此，由 sp_executesql 生成的 Transact-SQL 字符串比那些由 EXECUTE 语句生成的字符串更加相似。SQL Server 查询优化器可能将 sp_executesql 的 Transact-SQL 语句与以前所执行的语句的执行计划相匹配，从而节省编译新的执行计划的开销。

使用 EXECUTE 语句，所有参数值都必须转换为字符或 Unicode，并成为 Transact-SQL 字符串的一部分。

如果重复执行语句，则即使只有提供的参数值不同，每次执行时也必须生成全新的 Transact-SQL 字符串。这样就会以下列方式生成额外的开销：

  • 不断更改字符串文本（特别是复杂 Transact-SQL 语句）中的参数值，会影响 SQL Server 查询优化器将新的 Transact-SQL 字符串与现有执行计划相匹配的功能。
  • 每次执行时均必须重新生成整个字符串。
  • 每次执行时必须将参数值（非字符或 Unicode 值）转换为字符或 Unicode 格式。

sp_executesql 可以单独使用 Transact-SQL 字符串来设置参数值：

  
DECLARE @IntVariable INT;
DECLARE @SQLString NVARCHAR(500);
DECLARE @ParmDefinition NVARCHAR(500);

/* Build the SQL string one time. */
SET @SQLString =
     N'SELECT * FROM AdventureWorks.Sales.Store WHERE SalesPersonID = @SalesID';
/* Specify the parameter format one time. */
SET @ParmDefinition = N'@SalesID int';

/* Execute the string with the first parameter value. */
SET @IntVariable = 275;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @SalesID = @IntVariable;
/* Execute the same string with the second parameter value. */
SET @IntVariable = 276;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @SalesID = @IntVariable;

sp_executesql 还另有以下优点：

  • 因为 Transact-SQL 语句的实际文本在两次执行之间未更改，所以查询优化器能够将第二次执行中的 Transact-SQL 语句与第一次执行时生成的执行计划相匹配。因此，SQL Server 不必编译第二条语句。
  • Transact-SQL 字符串只生成一次。
  • 整型参数按其本身格式指定。不需要转换为 Unicode。
    注意：
    语句字符串中的对象名称必须完全符合 SQL Server 重用执行计划的要求。

在 SQL Server 早期版本中，唯一可以重用执行计划的方式是将 Transact-SQL 语句定义为存储过程并让应用程序执行该存储过程。这会产生管理应用程序的额外开销。使用 sp_executesql 可有助于减少此开销，并允许 SQL Server 仍重用执行计划。当提供给 Transact-SQL 语句的参数值只有一个变量时，如果要多次执行 Transact-SQL 语句，则可以使用 sp_executesql 而不要使用其他存储过程。因为 Transact-SQL 语句本身保持不变，仅参数值发生更改，所以 SQL Server 查询优化器可能会重用第一次执行时生成的执行计划。

下面的示例为服务器上的每个数据库（四个系统数据库除外）生成并执行 DBCC CHECKDB 语句。

  
USE master;
GO
SET NOCOUNT ON;
GO
DECLARE AllDatabases CURSOR FOR
SELECT name FROM sys.databases WHERE database_id > 4
OPEN AllDatabases

DECLARE @DBNameVar NVARCHAR(128)
DECLARE @Statement NVARCHAR(300)

FETCH NEXT FROM AllDatabases INTO @DBNameVar
WHILE (@@FETCH_STATUS = 0)
BEGIN
   PRINT N'CHECKING DATABASE ' + @DBNameVar
   SET @Statement = N'USE ' + @DBNameVar + CHAR(13)
      + N'DBCC CHECKDB (' + @DBNameVar + N')' + N'WITH PHYSICAL_ONLY'
   EXEC sp_executesql @Statement
   PRINT CHAR(13) + CHAR(13)
   FETCH NEXT FROM AllDatabases INTO @DBNameVar
END

CLOSE AllDatabases
DEALLOCATE AllDatabases
GO
SET NOCOUNT OFF;
GO

要执行的 Transact-SQL 语句包含绑定的参数标记时，SQL Server ODBC 驱动程序使用 sp_executesql 来实施 SQLExecDirect。这样，可以将 sp_executesql 提供的优势扩展到使用 ODBC 或通过 ODBC 定义的 API（例如 RDO）的所有应用程序。连接到 SQL Server 的现有 ODBC 应用程序无需重写就可以自动获得性能上的改进。但有一个例外，如果是执行时数据参数，则不能使用 sp_executesql。SQL Server Native Client ODBC 访问接口也使用 sp_executesql 来直接执行带有绑定参数的语句。使用 OLE DB 或 ADO 的应用程序无需重写就可以利用 sp_executesql 具有的优点

Giraffe_zj
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SQL sp_executesql详细使用
u011791378的专栏
09-02 2163
详情请查看microsoft： https://docs.microsoft.com/zh-cn/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-2017 CREATE TABLE [dbo].[Test]( [ID] [int] IDENTITY(1,...
SQLServer:探讨EXEC与sp_executesql的区别详解
09-10
在SQL Server中，`EXEC`和`sp_executesql`都是用于执行动态SQL语句的命令，但它们之间存在着显著的差异。这篇文章将深入探讨这两个命令的用途、特性和使用场景。 首先，`EXEC`命令主要用于执行存储过程或动态批处理...
使用 sp_executesql
weixin_30273931的博客
08-09 138
建议您在执行字符串时，使用 sp_executesql 存储过程而不要使用 EXECUTE 语句。由于此存储过程支持参数替换，因此 sp_executesql 比 EXECUTE 的功能更多；由于 SQL Server 2005 更可能重用 sp_executesql 生成的执行计划，因此 sp_executesql 比 EXECUTE 更有效。 自包含批处理 当 sp...
sp_executesql的用法
风雨无阻，执着追求!!!
04-23 1601
需求：表名是动态的，必须用exec来执行，然后在exec里边还得给变量动态赋值  这时候exec 就搞不定了 exec（'select @count=count(empid) from '+@tableName+' where proid='+@proid+' and id'+@id+' and state!=4'） 下边这个代码如果去掉where后边的东东就是可以的 一：正
SQL:exec sp_executesql 用法
Geovin Du Dream Park
07-16 665
--這種是無效的過程 declare @sql nvarchar(500), @where nvarchar(500),@i nvarchar(64),@p nvarchar(50),@id int set @id=5 set @sql='select '+@p+'=AreaCode from AdministrativeAreaList where AreaID='+cast(@id as v
请谨慎使用sp_executesql
爱码农爱生活
03-09 206
  前一段时间，给一位朋友公司做咨询，看到他们的很多的存储过程都存在动态sql语句执行，sp_executesql，即使在没有动态表名，动态字段名的情况下仍然使用sp_executesql,这个做法是不太明智的，会存在一些性能方面的问题。 先说说什么场景使用这个系统存储过程吧，sp_executesql,是sql server动态执行一段可以带有参数（内参，外参）的语句文本的系统存储过程，传入s...
SQL Server EXEC和sp_executesql的区别
11-19
### SQL Server EXEC与sp_executesql的区别详解 #### 一、引言 在SQL Server中，执行动态SQL或存储过程时，开发人员通常面临选择使用`EXEC`还是`sp_executesql`的问题。这两种方法虽然都能达到目的，但在功能、性能...
sysservers 中找不到服务器，请执行 sp_addlinkedserver 将该服务器添加到sysserver
09-11
对于其他版本的 SQL Server，尽管基本的解决思路相同，具体的步骤可能会略有差异。例如，不同版本的 SQL Server 可能会有不同的服务名称，或者可能需要通过SQL Server Management Studio (SSMS) 进行配置。在进行...
分享一下SQL Server执行动态SQL的正确方式
12-16
对比`EXECUTE`和`SP_EXECUTESQL`，我们可以看到`EXECUTE`能处理非Unicode和Unicode字符串，而`SP_EXECUTESQL`只接受Unicode字符串或可以隐式转换为`ntext`的变量。在处理包含变量的动态SQL时，`EXECUTE`可能遇到问题...
sybase与microsoft—sql的语法区别
03-17
- 在执行动态SQL时，Sybase使用`execute (@sql_string)`，而SQL Server使用`exec sp_executesql @sql_string`或`execute @sql_variable`。 3. **游标操作**： - Sybase使用`fetch from cursor_name`来获取游标中...
sp_executesql 用法
Golden的专栏
04-28 574
 一个传递两个参数的例子： declare @code varchar(50)declare @count intdeclare @StrSql nvarchar(500) --传入的Sql 需使用nvarchar类型declare @ParDefine nvarchar(500) --传入的参数定义 需使用nvarchar类型 set @code=101set @
sp_executesql 使用
weixin_34357887的博客
05-10 50
  这个挺有用可以动态选择表更新     CREATE PROCEDURE UP_HouseTemple_Update @Cur_Id int, @House_Id int, @House_Fw nvarchar(100), @House_Js int, @House_Dj nvarchar(100), @House_Mj float, @House_Price floa...
mssql中sp_executesql的用法
牛腩的专栏
12-22 681
上图： 浏览(1) | 评论(0) | 分类: SQL 评论
sp_executesql
最新发布
09-07
### 回答1： sp_executesql是一个SQL Server系统存储过程，可以用于执行动态SQL语句并返回结果。它的语法如下： ``` sp_executesql [@stmt =] N'sql语句', [@params =] N'参数定义', [@param1 =] '值1', ... ``` 其中，@stmt是要执行的SQL语句，@params是一个可选的字符串，用于定义参数。参数定义是一个以逗号分隔的字符串列表，每个参数由其名称、数据类型和方向组成。如果SQL语句中包含参数占位符（例如@parameter_name），则必须在@params字符串中定义该参数。然后，可以在@stmt中使用参数占位符来引用这些参数。 例如，以下代码演示了如何使用sp_executesql执行动态SQL语句，其中包含一个参数占位符： ``` DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM my_table WHERE id = @id' DECLARE @id INT = 1 EXEC sp_executesql @sql, N'@id INT', @id ``` 在上面的示例中，@sql包含要执行的SQL语句，其中包含一个参数占位符@id。然后，使用sp_executesql执行该语句，并将参数@id传递给它。这将返回my_table表中id为1的行。 ### 回答2： sp_executesql是用于在SQL Server数据库中执行动态SQL语句的系统存储过程。它提供了一种安全、高效的方式来执行动态生成的SQL语句，可以将参数传递给动态SQL语句，以避免SQL注入攻击，并且可以在执行相同的SQL语句时重用执行计划，提高性能。 sp_executesql的基本语法如下： sp_executesql [@stmt =] N'sql语句', [@params =] N'参数定义', [@param1 =] 值1, [@param2 =] 值2, ... 其中，@stmt是动态SQL语句的字符串，@params是定义参数的字符串，参数定义使用@参数名和数据类型的形式。 通过使用sp_executesql，可以将参数传递给动态SQL语句，以避免SQL注入攻击。例如，可以在动态SQL语句中使用参数@name来执行查询操作： DECLARE @name NVARCHAR(50) = N'John' DECLARE @sql NVARCHAR(1000) SET @sql = N'SELECT * FROM Customers WHERE CustomerName = @name' EXEC sp_executesql @sql, N'@name NVARCHAR(50)', @name 使用sp_executesql的另一个优点是，它可以在执行相同的SQL语句时重用执行计划，提高性能。例如，可以使用sp_executesql来执行一个频繁执行的动态SQL语句： DECLARE @sql NVARCHAR(1000) SET @sql = N'SELECT * FROM Orders WHERE OrderDate >= @startDate' EXEC sp_executesql @sql, N'@startDate DATETIME', '2022-01-01' EXEC sp_executesql @sql, N'@startDate DATETIME', '2022-02-01' 在执行第二个EXEC sp_executesql语句时，由于执行计划已经在第一个执行中生成，数据库引擎可以重用该计划，从而节省了资源和时间。 总之，sp_executesql是SQL Server数据库中的一个重要工具，可以安全、高效地执行动态SQL语句，并通过参数化和执行计划的重用提高性能。 ### 回答3： sp_executesql是一个存储过程，用于执行动态SQL语句。它可以接收动态生成的SQL语句以及参数列表作为输入，并执行这些语句。这个存储过程的语法如下： sp_executesql [@stmt =] N'sql语句', -- 需要执行的动态SQL语句 [@params =] N'参数定义列表', -- 参数列表的定义，包括参数的名称、数据类型和长度等信息 [@param1 =] '值1', -- 参数1 [@param2 =] '值2', -- 参数2 ... [@paramN =] '值N' -- 参数N 通过使用sp_executesql存储过程，我们可以在运行时动态生成SQL语句并将参数传递给它，从而实现更灵活的查询和操作数据库。使用sp_executesql的好处之一是可以防止SQL注入攻击，因为参数是作为参数传递给SQL语句，而不是直接拼接到字符串中。 在使用sp_executesql时，我们首先需要提供要执行的动态SQL语句，并使用参数占位符（如@ParamName）来表示需要传递的参数。然后，我们可以定义参数列表，包括参数的名称、数据类型和长度等信息。最后，我们可以通过传递参数的值来执行动态SQL语句。 例如，假设我们需要根据传入的城市名称查询员工信息。我们可以使用sp_executesql执行以下动态SQL语句： DECLARE @city NVARCHAR(50) SET @city = '北京' DECLARE @sql NVARCHAR(1000) SET @sql = N'SELECT * FROM Employee WHERE City = @CityParam' EXEC sp_executesql @sql, N'@CityParam NVARCHAR(50)', @CityParam = @city 通过这种方式，我们可以根据传入的城市名称动态生成SQL语句，并将参数传递给它，从而实现根据不同的城市查询员工信息的功能。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值