您的位置：首页 > 数据库 > 其它 > 正文

EXEC和sp_executesql的区别

更多 时间：2014-5-14 类别：数据库 浏览量：1076

EXEC和sp_executesql的区别

EXEC和sp_executesql的区别

一、实例需要的表和数据：

  •  
  • SQL 代码   复制
  • 
CREATE TABLE t_student(
 Id INT NOT NULL,
 Name NVARCHAR (10) NULL,
 Age TINYINT NULL,
 School NVARCHAR(20) NULL,
 Class NVARCHAR(10) NULL,
 Score FLOAT NULL,
 CONSTRAINT [PK_Student_Id] PRIMARY KEY CLUSTERED(Id)
)
GO

INSERT INTO t_student VALUES(1,'张小红',8,'育才小学','一班',92)
INSERT INTO t_student VALUES(2,'王丽丽',8,'育才小学','一班',90)
INSERT INTO t_student VALUES(3,'张燕',7,'云华小学','二班',86)
INSERT INTO t_student VALUES(4,'刘华',6,'云华小学','二班',85)

    •  

    二、EXEC的使用

     

    执行动态sql语句：

  •  
  • SQL 代码   复制
  • 
DECLARE @TableName NVARCHAR(50),@Sql NVARCHAR(MAX),@Score INT;
SET @TableName = 't_Student';
SET @Score = 90;
SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) +'WHERE Score >= '+CAST(@Score AS NVARCHAR(10))
EXEC (@sql);

    •  

    执行结果：

     

    注意：在执行拼接SQL 语句的时候，的EXEC括号中只允许包含一个字符串变量，但是可以串联多个变量，如果我们直接执行这个SQL语句：

  •  
  • 
--这是错误的调用
EXEC ('SELECT * FROM '+QUOTENAME(@TableName) +'WHERE Score >= '+CAST(@Score AS NVARCHAR(10)));

    •  

    执行就会提示错误。但是这样就没有问题：

  •  
  • SQL 代码   复制
  • 
DECLARE @TableName NVARCHAR(50),@Sql NVARCHAR(MAX),@Score INT
DECLARE @Sql2 NVARCHAR(MAX)
SET @TableName = 't_Student';
SET @Score = 90;
SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName)
SET @Sql2=' WHERE Score >= '+CAST(@Score AS NVARCHAR(10))
EXEC (@sql+@sql2)

    •  

    EXEC 执行拼接sql语句的时候不支持 嵌入式参数，如下：

  •  
  • 
DECLARE @OUT_Nums INT,@IN_Score INT,@Sql NVARCHAR(MAX)
SET @IN_Score = 90
SET @sql = 'SELECT @Nums=COUNT(1) FROM t_student WHERE Score >= @Score'
EXEC (@sql)

    •  

    说明备注

    通过上面的代码发现，EXEC 执行拼接的SQL语句的时候，不支持内嵌参数，包括输入参数和输出参数。有的时候我们想把得到的count(*)传出来，用EXEC是不好办到的。

     

     

    三、SP_EXECUTESQL

     

    SP_EXECUTESQL也是用来处理动态SQL 语句的。它比EXEC 更加灵活

     

    SP_EXECUTESQL的语法

    sp_executesql [ @stmt = ] stmt
    [
        {, [@params=] N'@parameter_name data_type [ OUT | OUTPUT ][,...n]' }
         {, [ @param1 = ] 'value1' [ ,...n ] }
    ]
     

    例如

  •  
  • SQL 代码   复制
  • 
DECLARE @TableName NVARCHAR(50),@Sql NVARCHAR(MAX),@Score INT;
SET @TableName = 't_Student';
SET @Score = 90;
SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) +'WHERE Score >= '+CAST(@Score AS NVARCHAR(10))
EXEC SP_EXECUTESQL @sql --注意这里没有了()
  •  
  •  
  • SQL 代码   复制
  • 
DECLARE @OUT_Nums INT,@IN_Score INT,@Sql NVARCHAR(MAX)
SET @IN_Score = 90
SET @sql = 'SELECT @Nums=COUNT(1) FROM t_student WHERE Score >= @Score'
EXEC SP_EXECUTESQL @sql,N'@Nums INT OUT,@Score INT',@OUT_Nums OUTPUT,@IN_Score
SELECT @OUT_Nums AS '人数'

    •  

    需要注意的是：

     

    1、要求动态Sql和动态Sql参数列表必须是NVARCHAR

     

    2、动态Sql的参数列表与外部提供值的参数列表顺序必需一致

    3、一旦使用了 '@name = value' 形式之后，所有后续的参数就必须以 '@name = value' 的形式传递，比如：

     

     

    四、EXEC 和 SP_EXECUTESQL 的区别

     

    1、 性能：

    官方描述：sp_executesql stmt 参数中的 Transact-SQL 语句或批处理在执行 sp_executesql 语句时才编译。随后，将编译 stmt 中的内容，并将其作为执行计划运行。该执行计划独立于名为 sp_executesql 的批处理的执行计划。sp_executesql 批处理不能引用调用 sp_executesql 的批处理中声明的变量。sp_executesql 批处理中的本地游标或变量对调用 sp_executesql 的批处理是不可见的。对数据库上下文所做的更改只在 sp_executesql 语句结束前有效。如果只更改了语句中的参数值，则 sp_executesql 可用来代替存储过程多次执行 Transact-SQL 语句。因为 Transact-SQL 语句本身保持不变，仅参数值发生变化，所以 SQL Server 查询优化器可能重复使用首次执行时所生成的执行计划。

     

    说通俗一点就是：如果用 EXEC 执行一条动态 SQL 语句，由于每次传入的参数不一样，所以每次生成的 @sql 就不一样，这样每执行一次SQL SERVER 就必须重新将要执行的动态 Sql 重新编译一次 。但是SP_EXECUTESQL 则不一样，由于将数值参数化，要执行的动态 Sql 永远不会变化，只是传入的参数的值在变化，那每次执行的时候就不用重新编译，速度和效率自然有所提升。

     

    2、灵活性

    从上面的例子我们已经能够看出 SP_EXECUTESQL 命令比 EXEC 命令更灵活，因为它提供一个接口，该接口及支持输入参数也支持输出参数。

     

    3、安全性

    EXEC 执行纯动态SQL，执行时可能无法使用预编译的执行计划，关键是不安全，可以导致 SQL 注入 ，而 SP_EXECUTESQL 执行参数化动态 SQL ,执行时能使用预编译的执行计划，而且保存存储过程时就可以确定可以使用的预编译的执行计划，而且最重要的是“安全”，天然免疫SQL 注入

     

    标签：数据库 SQL
  • 上一篇：如何提高代码可读性
  • 下一篇：SQL中的ISNULL的用法
    • 您可能感兴趣
    首页 编程学习 Web前端 数据库 软件设计
    开心学习 ©2013-2021 保留所有权利