动态sql语句基本语法--Exec与Exec sp_executesql 的区别

时间:2023-03-08 16:33:54

http://www.cnblogs.com/goody9807/archive/2010/10/19/1855697.html

动态sql语句基本语法

1   :普通SQL语句可以用Exec执行  
eg:       Select   *   from   tableName

Exec( 'select   *   from   tableName ')

Exec   sp_executesql   N 'select   *   from   tableName '         --   请注意字符串前一定要加N

2:字段名,表名,数据库名之类作为变量时,必须用动态SQL  
eg:

declare   @fname   varchar(20)   set   @fname   =   'FiledName '

Select   @fname   from   tableName          --   错误,不会提示错误,但结果为固定值FiledName,并非所要。

Exec( 'select   '   +   @fname   +   '   from   tableName ')           --   请注意   加号前后的   单引号的边上加空格  
当然将字符串改成变量的形式也可

declare   @fname   varchar(20)   set   @fname   =   'FiledName '   --设置字段名  
declare   @s   varchar(1000)   set   @s   =   'select   '   +   @fname   +   '   from   tableName '   Exec(@s)            --   成功

exec   sp_executesql   @s       --   此句会报错  
declare   @s   Nvarchar(1000)     --   注意此处改为nvarchar(1000)

set   @s   =   'select   '   +   @fname   +   '   from   tableName '

Exec(@s)                                 --   成功

exec   sp_executesql   @s       --   此句正确

3.   输出参数

declare   @num   int,   @sql   nvarchar(4000)

set   @sql= 'select   count(*)   from   tableName '

exec(@sql)  
--如何将exec执行结果放入变量中?  
declare   @num   int,   @sql   nvarchar(4000)

set   @sql= 'select   @a=count(*)   from   tableName   '

exec   sp_executesql   @sql,N '@a   int   output ',@num   output   select   @num

-----------------------

@maxnum  int  output --最大数量

declare @idcount int --用于存储 exec 语句赋值的变量

declare @maxpagesql  nvarchar(300)  --一定要nvarchaar

set @maxpagesql = 'select  @idcount=count(id) from QuestionInfo where 1=1 '

exec  sp_executesql  @maxpagesql,N'@idcount   int   output ',@maxnum   output

select @maxnum

动态SQL EXEC(http://www.cnblogs.com/Junelee1211/archive/2011/08/25/2153024.html)

1、EXEC命令的括号中只允许包含一个字符串变量,或者一个 字符串文本,或者字符串变量与字符串文本的串联。不能再括号中使用函数或CASE表达式,如下面尝试在括号中调用QUOTENAME函数以引用对象名称,运行将失败:

   1:  DECLARE @schemaname NVARCHAR(255),@tablename NVARCHAR(128)
   2:  SET @schemaname='dbo'
   3:  SET @tablename='Order Details'
   4:   
   5:  EXEC (N'SELECT COUNT(*) FROM '+QUOTENAME(@schemaname)+N'.'+QUOTENAME(@tablename)+N';')

上述代码将会产生如下错误:

消息 102,级别 15,状态 1,第 5 行    'QUOTENAME' 附近有语法错误。    SQL Server 分析和编译时间:       CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。

SQL Server 执行时间:       CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。

所以做好的方法是把代码构造到一个变量中,这样就不会受限制了,然后再把该变量作为EXEC命令的输入参数,就像这样:

   1:  DECLARE @schemaname NVARCHAR(255) ,
   2:      @tablename NVARCHAR(128) ,
   3:      @sql NVARCHAR(MAX)
   4:  SET @schemaname = 'dbo'
   5:  SET @tablename = 'Order Details'
   6:  SET @sql = N'SELECT COUNT(*) FROM ' + QUOTENAME(@schemaname) + N'.'
   7:      + QUOTENAME(@tablename) + N';'
   8:  EXEC (@sql)

2、EXEC不提供接口。EXEC(<string>)不提供接口。它唯一的输入就是包含你要调用代码的字符串。动态批处理不能访问在调用批处理中定义的局部变量。如下面代码尝试访问定义在调用批处理中的变量将失败。

   1:  DECLARE @i INT 
   2:  SET @i = 10248
   3:   
   4:  DECLARE @sql NVARCHAR(MAX)
   5:   
   6:  SET @sql = 'SELECT * FROM dbo.Orders WHERE OrderID=@i;'
   7:  EXEC(@sql)

将产生如下错误:

消息 137,级别 15,状态 2,第 1 行    必须声明标量变量 "@i"。

使用EXEC时,如果想访问变量,必须把变量内容串联到动态构建的 代码字符串中。

DECLARE @i INT
SET @i = 10248 DECLARE @sql NVARCHAR(MAX) SET @sql = 'SELECT * FROM dbo.Orders WHERE OrderID='
+ CAST(@i AS NVARCHAR(10)) + ';'
EXEC(@sql)

这样就没有问题了。

如果一个变量包含字符串,把该变量的内容串联到代码将会导致安全风险(SQL注入),为了避免SQL注入,可以吧字符串大小限制为所需的最小长度。当然,实际中这种情况根本不需要动态SQL直接执行SQL语句就可以,这个示例只是为了演示。

串联变量的内容存在性能方面的弊端,SQL Server将为每个唯一的查询字符串创建新的即席执行计划,即使查询模式相同也是这样的。为演示这一点,先清空缓存中的执行计划。

DBCC FREEPROCCACHE
将上端代码执行三次,分别为@i赋值10248,10249和10250,然后使用下面的代码查询
   1:  SELECT  cacheobjtype ,
   2:          objtype ,
   3:          usecounts ,
   4:          sql
   5:  FROM    sys.syscacheobjects
   6:  WHERE   sql NOT LIKE '%cache%'
   7:          AND sql NOT LIKE '%sys.%'



得到查询结果:

cacheobjtype    objtype    usecounts    sql    Compiled Plan    Adhoc    1    SELECT * FROM dbo.Orders WHERE OrderID=10250;    Compiled Plan    Adhoc    1    SELECT * FROM dbo.Orders WHERE OrderID=10248;    Compiled Plan    Prepared    3    (@1 smallint)SELECT * FROM [dbo].[Orders] WHERE [OrderID]=@1    Compiled Plan    Adhoc    4    SET STATISTICS IO ON SET STATISTICS TIME ON    Compiled Plan    Adhoc    1    SELECT * FROM dbo.Orders WHERE OrderID=10249;    Compiled Plan    Adhoc    4    SET STATISTICS IO OFF SET STATISTICS TIME OFF

EXEC除了不支持动态批处理中的输入参数外,也不支持输出参数。默认情况下,EXEC把查询输出返回给调用者。如果你想把输出结果返回给调用批处理中的变量,事情就没那么简单了,为此,你需要使用INSERT EXEC把输出插入到一个目的表,然后再从该表中取值,赋给该变量,就像这样:

   1:  DECLARE @schemaname NVARCHAR(128) ,
   2:      @tablename NVARCHAR(128) ,
   3:      @colname NVARCHAR(128) ,
   4:      @sql NVARCHAR(MAX) ,
   5:      @cnt INT
   6:      
   7:  SET @schemaname = 'dbo'
   8:  SET @tablename = 'Orders'
   9:  SET @colname = 'CustomerID'
  10:       
  11:  SET @sql = N'SELECT COUNT(DISTINCT ' + QUOTENAME(@colname) + ') FROM '
  12:      + QUOTENAME(@schemaname) + N'.' + QUOTENAME(@tablename) + N';'
  13:      
  14:  CREATE TABLE #T1 ( cnt INT )
  15:  INSERT  INTO #T1
  16:          EXEC ( @sql
  17:              )
  18:  SELECT  @cnt = cnt
  19:  FROM    #T1
  20:  SELECT  @cnt
  21:  DROP TABLE #T1

3、在SQL Server2000中串联变量值时,EXEC比sp_executesql多一个优势,它支持更长的代码,尽管技术上sp_executesql的输入代码字符串是NTEXT类型的,但你一般是在局部变量中构造代码字符串。而你又不能用大型对象类型声明局部变量,所以,实际上在sp_executesql中执行的查询字符串被限制为Unicode字符串(NVARCHAR)支持的最大长度4000,而EXEC支持常规字符串(VARCHAR)允许最大8000个字符。另外EXEC还支持一个特殊的功能,它允许你在括号中串联多个变量,每个变量都支持8000个字符的长度。

在SQL Server2005中,就不用这么纠结了,因为可以为EXEC命令提供一个VARCHAR(MAX)或NVARCHAR(MAX)的变量作为输入,输入字符串可以达到2GB大小