SP_EXECUTESQL N’SELECT * FROM Groups’; –执行成功
SP_EXECUTESQL ‘SELECT * FROM Groups’ –执行出错
Summary:EXECUTE 可以执行非Unicode或Unicode类型的字符串常量、变量.而SP_EXECUTESQL只能执行Unicode或可以隐式转换为ntext的字符串常量、变量.
DECLARE @GroupName VARCHAR(50);SET@GroupName =’SuperAdmin’;
EXECUTE(‘SELECT * FROM Groups WHERE GroupName=”’ + SUBSTRING(@GroupName, 1,5) + ””); –‘SUBSTRING’ 附近有语法错误.
DECLARE @Sql VARCHAR(200);
DECLARE @GroupName VARCHAR(50);SET@GroupName =’SuperAdmin’;
SET@Sql=’SELECT * FROM Groups WHERE GroupName=”’ + SUBSTRING(@GroupName, 1,5) + ””
–PRINT @Sql;EXECUTE(@Sql);
Summary:EXECUTE 括号里面只能是字符串变量、字符串常量、或它们的连接组合,不能调用其它一些函数、存储过程等. 如果要使用,则使用变量组合,如上所示.
DECLARE @Sql VARCHAR(200);
DECLARE @GroupName VARCHAR(50);SET@GroupName =’SuperAdmin’;
SET@Sql=’SELECT * FROM Groups WHEREGroupName=@GroupName’
–PRINT @Sql;EXECUTE(@Sql); –出错:必须声明标量变量 “@GroupName”.SET@Sql=’SELECT * FROM Groups WHERE GroupName=’ + QUOTENAME(@GroupName, ””)
EXECUTE(@Sql); –正确:
DECLARE @Sql NVARCHAR(200);
DECLARE @GroupName NVARCHAR(50);SET@GroupName =’SuperAdmin’;
SET@Sql=’SELECT * FROM Groups WHEREGroupName=@GroupName’
PRINT @Sql;
EXEC SP_EXECUTESQL @Sql,N’@GroupNameNVARCHAR’,@GroupName
查询出来没有结果,没有声明参数长度.
DECLARE @Sql NVARCHAR(200);
DECLARE @GroupName NVARCHAR(50);SET@GroupName =’SuperAdmin’;
SET@Sql =’SELECT * FROM Groups WHERE GroupName=@GroupName’
PRINT @Sql;
EXEC SP_EXECUTESQL @Sql, N’@GroupName NVARCHAR(50)’,@GroupName
Summary:动态批处理不能访问定义在批处理里的局部变量 . SP_EXECUTESQL 可以有输入输出参数,比EXECUTE灵活.
下面我们来看看EXECUTE , SP_EXECUTESQL的执行效率,首先把缓存清除执行计划,然后改变用@GroupName值SuperAdmin、CommonUser、CommonAdmin分别执行三次.然后看看其使用缓存的信息
DBCC FREEPROCCACHE;
DECLARE @Sql VARCHAR(200);
DECLARE @GroupName VARCHAR(50);SET@GroupName =’SuperAdmin’; –‘CommonUser’, ‘CommonAdmin’
SET@Sql =’SELECT * FROM Groups WHERE GroupName=’ + QUOTENAME(@GroupName, ””)
EXECUTE(@Sql); SELECTcacheobjtype, objtype, usecounts, sql
FROM sys.syscacheobjects
