sql server 存储过程中执行动态Sql语句

发布时间:2021-01-06编辑:脚本学堂
本文介绍下,sql server的存储过程中执行动态sql语句的方法,学习下sql server 动态sql语句的用法,有需要的朋友参考下。
本节是 sql server 存储过程中执行动态Sql语句 第二部分的内容,请大家继续阅读。
说明:
@stmt参数是输入的动态批处理,它可以引入输入参数或输出参数,和存储过程的主体语句一样,只不过它是动态的,而存储过程是静态的,不过你也可以在存储过程中使用sp_executesql;
@params参数与定义输入/输出参数的存储过程头类似,实际上和存储过程头的语法完全一样;
@<params assignment> 与调用存储过程的EXEC部分类似。

其实@stmt,@params可以省略,那么exec sp_executesql的语法就可以简写成如下格式:
 

复制代码 代码示例:
EXEC sp_executesql
   <statement>,
   <params>,
   <params assignment>
 

为了说明sp_executesql对执行计划的管理优于EXEC,将使用前面讨论EXEC时用到的代码。

例如:
 

复制代码 代码示例:

DECLARE @TableName VARCHAR(50),@sql NVARCHAR(MAX),@OrderID INT;  
      SET @TableName = 'Orders ';
      SET @OrderID = 10251;  
      SET @sql = 'SELECT * FROM '+@TableName + ' WHERE OrderID = @OID ORDER BY ORDERID DESC' 
      --注意当要对动态sql语句的表名实行参数化时,不可以如下表示:
      --set @sql='select * from @TableName where OrderID=@OID ORDER BY Orderid desc',
     --如果这样会提示必须声明标量变量@TableName,只可以如上面所写的一样,将表名@TableName作为变量名进行拼接

      EXEC sp_executesql  
              @sql,  
             N'@OID int ',  
            @OID = @OrderID

下面看看exec sp_executesql的执行效率,在调用该代码和检查它生成的执行计划前,先清空缓存中的执行计划;
DBCC FREEPROCCACHE

将上面的动态代码执行3次,每次执行都赋予@OrderID 不同的值,然后查询sys.syscacheobjects表,并注意它的输出,优化器只创建了一个备用计划,而且该计划被重用的3次。

例如:
 

复制代码 代码示例:
SELECT cacheobjtype,objtype,usecounts,sql FROM sys.syscacheobjects
  WHERE sql NOT LIKE '%cache%' AND sql NOT LIKE '%sys.%' AND sql NOT LIKE '%sp_executesql%'
 

点击F5运行。

sq_executesql的另一个与其接口有关的强大功能是,你可以使用输出参数为调用批处理中的变量返回值。
利用该功能可以避免用临时表返回数据,从而得到更高效的代码和更少的重新编译。定义和使用输出参数的语法与存储过程类似。

即需要在声明参数时指定OUTPUT子句。

例如,演示如何从动态批处理中利用输出参数@p把值返回到外部批处理中的变量@i.
 

复制代码 代码示例:
DECLARE @sql AS NVARCHAR(12),@i AS INT;
  SET @sql = N' SET @p = 10';
  EXEC sp_executesql
         @sql,
        N'@p AS INT OUTPUT',
        @p = @i OUTPUT
   SELECT @i  --该代码返回输出10
 

以字母 N 为前缀标识 Unicode 字符串常量

总结:
1,使用exce sp_executesql效率比exec要高,同一类型的语句,只需编译一次即可,而exec执行几次就需要编译几次。
2,构造动态sql的where子句,也就是条件子句时,exec无法使用变量来进行站位,需要将变量转换成字符串,然后和动态sql进行拼接,这就可能引起sql注入问题。

例如:
 

复制代码 代码示例:
SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) +
   ' WHERE OrderID = '+CAST(@OrderID AS VARCHAR(50)) + ' ORDER BY ORDERID DESC'
 

而若使用exec sp_executesql则可以使用变量来进行站位,以后再给这个参数传值的放式构造动态sql,就避免的Sql注入的问题,如下:
 

复制代码 代码示例:
SET @sql = 'SELECT * FROM '+@TableName + ' WHERE OrderID = @OID ORDER BY ORDERID DESC' 

3,无论是Exec还是Exec sp_executesql,如果想要将表名和列名进行动态参数化,不可以使用表名参数和列名参数来进行站位,而且表名参数和列名参数需要使用存储过程的参数。
对于exec sp_executesql来说,不可以将表名参数和列名参数在指定为在exec sp_executesql参数声明部分声明的参数。

例如:
 

复制代码 代码示例:
create PROCEDURE GetData
    @tbName nvarchar(10),
    @colName nvarchar(10),
    @Name nvarchar(10)
   AS
    BEGIN
    declare @sql nvarchar(50);
    set @sql='select '+ @colName+'  from ' +@tbName+ ' where name=@whereName';
    --注意此句不可以写成如下:
    -- set @sql='select @colName from @tbName where name=@whereName';
    exec sp_executesql
     @sql,
     N'@whereName nvarchar(10)',
     @Name
    END
 

即exec sp_executesql语句的参数声明部分只能声明动态sql的where子句的参数。