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

   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

   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大小

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

标签：