Execute 与 sp_Executesql

(一) 使用EXECUTE命令运行命令字符串
要使用EXECUTE命令来运行一个命令字符串的语法如下：
EXEC[UTE] ({@string_variable|[N]'stql_string'} [+...n])
从语法看出，可以先将包含所要运行的命令的字符串赋给一个局部变量@string_variable，再使用EXECUTE命令来运行，或是直接使用EXECUTE命令去运行一个包含所要运行的命令语句的字符串。此外，您也可以将多个包含所要运行的命令语句的字符串相加在一起，再交给EXECUTE命令来一次运行。
在使用EXECUTE命令时必须注意以下几点：
a、在EXECUTE命令中必须将命令字符串包含在一对小括号中；
b、局部变量@string_variable可以是数据类型char、varchar、nchar或nvarchar。如果您在'stql_string'之前加上了
大写的N，此时该字符串将被解释为nvarchar数据类型；
交给EXECUTE命令运行的命令字符串必须全部是由字符数据组成，也就是说您必须自行将所有的数值数据转换成字符数据；
d、EXECUTE命令允许以嵌套方式使用；
e、EXECUTE命令可以使用前四个字符的缩写方式，即EXECUTE与EXEC都是正确的写法。
举例如下：
declare @dbName varchar(20),
@tbName varchar(20),
@sqlString1 varchar(100),
@sqlString2 varchar(100)
set @dbName='Northwind'
set @tbName='Customers'
-- 组合出所需要的命令字符串
set @sqlString1='use'+@dbName+char(13)  --char(13)换行
-- 组合出所需要的命令字符串
set @sqlString2='select * from'+@tbName
-- 将命令字符串包含在一对小括号中后交给EXEC命令运行
exec(@sqlString1+@sqlString2)
(二) 使用系统存储过程sp_executesql运行Unicode命令字符串
语法如下：
sp_executesql [@stmt=] stmt
{,[@params=]N'@parameter_name data_type [,...n]'}
{,[@param1=] 'value' [,...n]}
说明：必须先将各个包含所要运行的命令语句的Unicode字符串相加在一起，再交给系统存储过程sp_executesql来运行，而不能在sp_executesql的语句中来相加各个命令字符串。
举例如下：
declare @id int
declare @count int
declare @sql nvarchar(4000)
set @id = 11
set @sql = 'select @num=count(*) from ebook where id<=@id'
exec sp_executesql
  @sql,' --要执行的sql语句（带嵌入参数）
  @num int out,@id int', --定义嵌入参数
  @count output,@id --和调用存储过程差不多,给嵌入参数赋值（如果存在输出参数,则指定输出参数的接受变量）
(三) 成批处理原则
不论你使用EXECUTE命令还是系统存储过程sp_executesql来运行命令字符串，被运行的命令字符串将成为一个批处理。SQL Server会将命令字符串中的T-SQL命令语句独立编译至一个运行语句，即会与EXECUTE命令或系统存储过程sp_executeslq所在批处理的运行计划分隔开来。
为此，我们需要注意以下几点：
a、无法在命令字符串中访问EXECUTE或sp_executesql所在批处理中的变量；反之，包含EXECUTE或sp_executesql的批处理也无法访问命令字符串中所声明的变量或局部游标。
b、如果运行期间字符串中使用了USE命令改变了当前数据库，当前数据库将限于运行字符串的运行期间，运行完毕后原来的当前数据库仍旧是当前数据库。如下面代码中，虽然在命令字符串中将当前数据库设置成“Northwind”，但是在命令字符串运行完毕后，当前数据库仍旧是当前数据库pubs：
USE pubs
--此时当前数据库为Northwind'
exec sp_executesql N'USE Northwind'
go
--此时当前数据库仍旧为pubs
select count(*) from authors
go
(四) 参数替换功能
交给EXECUTE命令运行的命令字符串必须全部都由字符数据组成，此举造成的最大困扰就是---每当参数值改变时就必须再转换一次(即必须重建整个命令字符串)，换句话说，EXECUTE命令不具备参数替换能力。
相反，sp_executesql具备参数替换功能，并会带来如下好处：
a、T-SQL字符串只需创建一次；
b、参数可以沿用其初始的类型与格式，不需要换成Unicode字符串；
c、由于每次运行的T-SQL命令语句没有改变，查询优化器(Query Optimizer)会直接将为第一次生成的运行计划应用到第二次运行的T-SQL命令语句，如此使SQL Server不需要再次编译，提高了运行效率。
事实上， 要连续多次运行某条T-SQL命令语句时，而仅有其中的参数值改变时，使用sp_executesql的做法会比使用存储过程更有效率。原因之一是运行计划会被反复使用，原因之二是可以省去存储过程的维护成本。
但是，请注意：要使运行计划能够被反复使用，名列字符串必须以三部分名称来引用对象！
举例1如下：
--由于sp_executesql具备参数替换功能，因此在创建命令字符串时无须先将所有的参数值转换成字符串
declare @sqlString nvarchar(500)
set @sqlString=
N'select LastName+FirstName,HireDate from Northwind.dbo.Employees '+N'whereEmployeeID=@EmployeeID'
exec sp_executesql
  @sqlString,  --要执行的带嵌入参数的sql字符串
  N'@EmployeeIDint',  --定义命令字符串中参数的数据类型
  1  --设置参数的值
举例2如下：
declare @sqlString nvarchar(500)
declare @parameterDefinition nvarchar(500)
declare @givenValue int
set @sqlString=
N'select LastName+FirstName,HireDate from Northwind.dbo.Employees '+N'whereEmployeeID=@employeeID'
--设置参数定义字符串
set @parameterDefinition=N'@employeeID int'
--先将参数数值存入变量，稍后再将变量赋给命令字符串中的参数
set @givenValue=1
exec sp_executesql
  @sqlString,
  @parameterDefinition,
  @employeeID=@givenValue --明确赋给参数的数据值
--设置参数定义字符串
set @parameterDefinition=N'@EmployeeID int'
--先将参数数值存入变量，稍后再将变量赋给命令字符串中的参数
set @givenValue=2
exec sp_executesql
  @sqlString,
  @parameterDefinition,
  @employeeID=@givenValue --明确赋给参数的数据值