理解sp_excutesql存储过程

以下是对msdn中关于sp_excutesql存储过程描述的理解：

msdn关于sp_excutesql的描述分成三个方面：

• 自包含批处理
• 替换参数值
• 重用执行计划

自包含批处理：

理解自包含批处理最好先理解“批处理”的概念。“批处理”是个名词，msdn中对批处理的解释是：批处理是同时从应用程序发送到 SQL Server 并得以执行的一组单条或多条 Transact-SQL 语句。SQL Server 将批处理的语句编译为单个可执行单元，称为执行计划。

自包含批处理就是：当 sp_executesql 或 EXECUTE 语句执行时，语句中的字符串将作为它的自包含批处理执行，也就是说sp_executesql 或 EXECUTE 语句中的字符串会被编译成独立的执行计划。例如：

DECLARE @CharVariable CHAR(3);SET @CharVariable = 'abc';
EXECUTE sp_executesql N'PRINT @CharVariable';GO

上面语句的其实是两个批处理，首先是go语句之上（包括go语句）的是一个批处理，sp_executesql N'PRINT @CharVariable';是另一个批处理（自包含批处理）。SQL Server将这两个批处理分别编译成执行单元。所以上面的例子在执行时会报以下错误：必须声明标量变量 "@CharVariable"。也就是说sp_executesql 存储过程根本访问不到"@CharVariable变量。

另外，还有以下几点需要注意：

（1）在执行sp_executesql 或 EXECUTE 语句之前，不会将它的自包含批处理编译到执行计划；在执行自包含批处理之前，不会分析或检查其错误，执行时再检查。

（2）如果在sp_executesql 或 EXECUTE 的自包含批处理中包含一个更改数据库上下文的USE语句，那么这个更改仅持续到sp_executesql 或 EXECUTE 语句运行结束。例如：

USE master;
GO
EXECUTE sp_executesql N'USE AdventureWorks2008R2;'
GO
/* This statement fails because the database context
   has now returned to master. */
SELECT * FROM Sales.Store;
GO
上面的例子包含三个批处理，其中一个是sp_excutesql的自包含批处理。这个例子将不能正确运行，因为自包含批处理中虽然将数据库设置成了AdventureWorks2008R2，但是该语句执行完后，数据库又立刻换成了master，所以select语句中将找到不到Sales.Store对象。

替换参数值：

这一点msdn讲的非常全面而且细致，它在sp_executesql 或 EXECUTE的对比中得到体现：

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

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

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

（1）不断更改字符串文本（特别是复杂 Transact-SQL 语句）中的参数值，会影响 SQL Server 查询优化器将新的 Transact-SQL 字符串与现有执行计划相匹配的功能。

（2）每次执行时均必须重新生成整个字符串。

（3）每次执行时必须将参数值（非字符或 Unicode 值）转换为字符或 Unicode 格式。

上面是msdn的原话，本来想用自己的语言再描述一遍，但是发现msdn的描述已经很简洁明了，不用画蛇添足了。

 

重用执行计划：

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

实际上，我们在应用程序中多数不会直接使用sp_executesql 作为存储过程的名称，因为它不具有任何含义，理解起来不方便。