SQl的execute 与sp_executesql

 

SQL语句类似string.Format()组装变量的使用。

一般来说，我们会在程序运行期间将所要运行的命令语句组合成一个字符串，然后使用EXECUTE命令或系统存储过程

@0sp_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_namedata_type [,...n]'}

{,[@param1=] 'value' [,...n]}

 

说明：必须先将各个包含所要运行的命令语句的Unicode字符串相加在一起，再交给系统存储过程sp_executesql来运行，而不能在sp_executesql的语句中来相加各个命令字符串。

举例如下：

declare @DBName nvarchar(20),

@Tbname nvarchar(20),

@SQLString nvarchar(500)

set @DBName=N'Northwind'

set @Tbname=N'Customers'

set @SQLString=N'USE'+@DBName+char(13) --char(13)换行

SET @SQLString=@SQLString+N'select * from'+@Tbname

必须先将命令字符串组合完毕后再交给sp_executesql来运行

exec sp_executesql @SQLString

(三) 成批处理原则

不论你使用EXECUTE命令还是系统存储过程sp_executesql来运行命令字符串，被运行的命令字符串将成为一个批处理。SQL Server会将命令字符串中的T-SQL命令语句独立编译至一个运行语句，即会与EXECUTE命令或系统存储过程sp_executeslq所在批处理的运行计划分隔开来。

为此，我们需要注意以下几点：

a、无法在命令字符串中访问EXECUTE或sp_executesql所在批处理中的变量；反之，包含EXECUTE或sp_executesql的批处理也无法访问命令字符串中所声明的变量或局部游标。

b、如果运行期间字符串中使用了USE命令改变了当前数据库，当前数据库将限于运行字符串的运行期间，运行完毕后原来的当前数据库仍旧是当前数据库。如下面代码中，虽然在命令字符串中将当前数据库设置成“Northwind”，但是在命令字符串运行完毕后，当前数据库仍旧是当前数据库pubs：

USE pubs

--此时当前数据库为

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,

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 --明确赋给参数的数据值