您好,欢迎光临本网站![请登录][注册会员]  
文件名称: SQL Server 中 EXEC 与 SP_EXECUTESQL 的区别.doc
  所属分类: SQLServer
  开发工具:
  文件大小: 108kb
  下载次数: 0
  上传时间: 2019-08-30
  提 供 者: qq_45******
 详细说明: SQL Server 中 EXEC 与 SP_EXECUTESQL 的区别 MSSQL为我们提供了两种动态执行SQL语句的命令,分别是 EXEC 和 SP_EXECUTESQL ,我们先来看一下两种方式的用法。 先建立一个表,并添加一些数据来进行演示: 复制代码 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 C LUSTERED(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 EXEC命令可以执行一个存储过程也可以执行一个动态SQL语句。先来看看怎么执行存储过程: 新建一个存储过程 SP_GetStudent ,返回 成绩大于90 分的学生: 复制代码 CREATE PROCEDURE [dbo].[Sp_GetStudent] Score FLOAT, Nums INT OUTPUT AS BEGIN SET NOCOUNT ON; SELECT * FROM t_student WHERE Score >=Score SELECT Nums=COUNT(1) FROM t_student WHERE Score >=Score IF(Nums>0) RETURN 1 ELSE RETURN 0 END GO 复制代码 该存储过程涉及了 查询操作、返回值和输出参数,我们来看用EXEC 命令如何调用: 复制代码 DECLARE return_value int, OutNums int EXEC return_value = [dbo].[Sp_GetStudent] Score = 90, Nums = OutNums OUTPUT SELECT OutNums as N大于90分的人数 SELECT 返回值 = return_value GO 复制代码 执行结果: 我们发现EXEC 执行存储过程和我们平时程序执行一个方法是几乎一样的,返回值参数 直接就可以等于存储过程的执行后的返回值,输出参数 在后面需要增加 OUTPUT 关键字。 执行存储过程不是重点,重点是执行动态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))); 执行就会提示错误。但是这样就没有问题: 复制代码 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: SP_EXECUTESQL 是在 SQL 2005中引入的新的系统存储过程,也是用来处理动态SQL 语句的。它比EXEC 更加灵活,首先也执行一下第一次的拼接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 --注意这里没有了() 执行结果: SP_EXECUTESQL 支持内嵌参数: 先来看一下SP_EXECUTESQL的语法: sp_executesql [ stmt = ] stmt [ {, [params=] Nparameter_name data_type [ OUT | OUTPUT ][,...n] } {, [ param1 = ] value1 [ ,...n ] } ] 说明: [ stmt = ] stmt 包含 Transact-SQL 语句或批处理的 Unicode 字符串。stmt 必须是 Unicode 常量或 Unicode 变量。不允许使用更复杂的 Unicode 表达式(例如使用 + 运算符连接两个字符串)。不允许使用字符常量。如果指定了 Unicode 常量,则必须使用 N 作为前缀。例如,Unicode 常量 Nsp_who 是有效的,但是字符常量 sp_who 则无效。字符串的大小仅受可用数据库服务器内存限制。在 64 位服务器中,字符串大小限制为 2 GB,即 nvarchar(max) 的最大大小。stmt 中包含的每个参数在 params 参数定义列表和参数值列表中均必须有对应项 [ params = ] Nparameter_namedata_type[ ,... n ]  包含 stmt 中嵌入的所有参数定义的字符串。字符串必须是 Unicode 常量或 Unicode 变量。每个参数定义由参数名称和数据类型组成。n 是表示附加参数定义的占位符。在 stmt 中指定的每个参数必须在 params 中定义。如果 stmt 中的 Transact-SQL 语句或批处理不包含参数,则不需要 params。该参数的默认值为 NULL。 [ param1 = ] value1 参数字符串中定义的第一个参数的值。该值可以是 Unicode 常量,也可以是 Unicode 变量。必须为 stmt 中包含的每个参数提供参数值。如果 stmt 中的 Transact-SQL 语句或批处理没有参数,则不需要这些值。 [ OUT | OUTPUT ] 指示参数是输出参数。除非是公共语言运行 (CLR) 过程,否则 text、ntext 和 image 参数均可用作 OUTPUT 参数。使用 OUTPUT 关键字的输出参数可以为游标占位符,CLR 过程除外。 n 附加参数值的占位符。这些值只能为常量或变量,不能是很复杂的表达式(例如函数)或使用运算符生成的表达式。 返回代码值 : 0(成功)或非零(失败) 结果集:从生成 SQL 字符串的所有 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,NNums INT OUT,Score INT,OUT_Nums OUTPUT,IN_Score SELECT OUT_Nums AS 人数 执行结果: 需要注意的是: 1、要求动态Sql和动态Sql参数列表必须是NVARCHAR 2、动态Sql的参数列表与外部提供值的参数列表顺序必需一致 3、一旦使用了 name = value 形式之后,所有后续的参数就必须以 name = value 的形式传递,比如: 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 stmt=sql,params=NNums INT OUT,Score INT,Nums=OUT_Nums OUTPUT,Score=IN_Score SELECT OUT_Nums AS 人数 通过上面的例子已经很清晰的表明了,在执行动态SQL 语句的时候,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 注入 作者:Rising Sun 出处:http://www.cnblogs.com/lxblog/ 本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利.
(系统自动生成,下载前可以参看下载内容)

下载文件列表

相关说明

  • 本站资源为会员上传分享交流与学习,如有侵犯您的权益,请联系我们删除.
  • 本站是交换下载平台,提供交流渠道,下载内容来自于网络,除下载问题外,其它问题请自行百度
  • 本站已设置防盗链,请勿用迅雷、QQ旋风等多线程下载软件下载资源,下载后用WinRAR最新版进行解压.
  • 如果您发现内容无法下载,请稍后再次尝试;或者到消费记录里找到下载记录反馈给我们.
  • 下载后发现下载的内容跟说明不相乎,请到消费记录里找到下载记录反馈给我们,经确认后退回积分.
  • 如下载前有疑问,可以通过点击"提供者"的名字,查看对方的联系方式,联系对方咨询.
 相关搜索:
 输入关键字,在本站1000多万海量源码库中尽情搜索: