spexecutesql:SQL Server EXEC和sp_executesql的区别来源: 发布时间:星期二, 2009年2月3日 浏览:2次 评论:0
="t18">1,EXEC
 使用2  sp_executesql使用MSSQL为我们提供了两种动态执行SQL语句 命令分别是EXEC和sp_executesql;通常,sp_executesql则更具有优势它提供了输入输出接口而EXEC没有 还有 个最大好处就是利用sp_executesql能够重用执行计划这就大大提供了执行性能(对于这个我在后面例子中会详加介绍说明)还可以编写更安全代码EXEC在某些情况下会更灵活除非您有令人信服理由使用EXEC否侧尽量使用sp_executesql.1,EXEC 使用EXEC命令有两种使用方法 种是执行个存储过程另种是执行个动态批处理以下所讲都是第 2种使用方法下面先使用EXEC演示 个例子,代码1DECLARE @TableName VARCHAR(50),@Sql NVARCHAR(MAX),@OrderID INT; SET @TableName = 'Orders'; SET @OrderID = 10251; SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) +'WHERE OrderID = '+CAST(@OrderID AS VARCHAR(10))+' ORDER BY ORDERID DESC' EXEC(@sql); 注:这里 EXEC括号中只允许包含个 串变量但是可以串联多个变量如果我们这样写EXEC:EXEC('SELECT TOP('+ CAST(@TopCount AS VARCHAR(10)) +')* FROM '+QUOTENAME(@TableName) +' ORDER BY ORDERID DESC'); SQL编译器就会报错 编译不通过而如果我们这样:EXEC(@sql+@sql2+@sql3); 编译器就会通过； 所以最佳 做法是把代码构造到个变量中然后再把该变量作为EXEC命令输入参数这样就不会受限制了；EXEC不提供接口 这里 接口是指它不能执行个包含个带变量符批处理这里乍听好像不明白不要紧我在下面有个例子您看就知道什么意思.DECLARE @TableName VARCHAR(50),@Sql NVARCHAR(MAX),@OrderID INT; SET @TableName = 'Orders'; SET @OrderID = 10251; SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) +'WHERE OrderID = @OrderID ORDER BY ORDERID DESC' EXEC(@sql); 关键就在SET @sql这 句话中如果我们运行这个批处理编译器就会产生下 Msg 137, Level 15, State 2, Line 1 必须声明标量变量 "@OrderID" 使用EXEC时 如果您想访问变量必须把变量内容串联到动态构建代码串中如:SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) +'WHERE OrderID = '+CAST(@OrderID AS VARCHAR(10))+' ORDER BY ORDERID DESC'串联变量 内容也存在性能方面弊端SQL Server为每个查询串创建新执行计划即使查询模式相同也是这样为演示这点先清空缓存Cache中执行计划DBCC FREEPROCCACHE (这个不是本文所涉及 内容您可以查看MSMSDN)http://msdn.microsoft.com/zh-cn/library/ms174283.aspx 将代码1运行3次 分别对@OrderID 赋予下面3个值102511025210253然后使用下面代码查询SELECT cacheobjtype,objtype,usecounts,sql FROM sys.syscacheobjects WHERE sql NOT LIKE '%cach%' AND sql NOT LIKE '%sys.%' 点击F5运行 就会出现下面如图所示查询结果:我们可以看到 每执行次都要产生次编译执行计划没有得到充分重用EXEC除了不支持动态批处理中 输入参数外他也不支持输出参数默认情况下EXEC把查询输出返回给 者例如下面代码返回Orders表中所有记录数DECLARE @sql NVARCHAR(MAX) SET @sql = 'SELECT COUNT(ORDERID) FROM Orders'; EXEC(@sql); 然而 如果你要把输出返回给批处理中变量事情就没有那么简单了为此你必须使用INSERT EXEC语法把输出插入到个目标表中然后从这表中获取值后赋给该变量就像这样: DECLARE @sql NVARCHAR(MAX),@RecordCount INT SET @sql = 'SELECT COUNT(ORDERID) FROM Orders'; CREATE TABLE #T(TID INT); INSERT INTO #T EXEC(@sql); SET @RecordCount = (SELECT TID FROM #T) SELECT @RecordCount DROP TABLE #T 2,sp_executesql 使用sp_executesql命令在SQL Server中引入 比EXEC命令晚些它主要为重用执行计划提供更好支持为了和EXEC作 个鲜明对比,我们看看如果用代码1代码把EXEC换成sp_executesql,看看是否得到我们所期望结果DECLARE @TableName VARCHAR(50),@sql NVARCHAR(MAX),@OrderID INT ,@sql2 NVARCHAR(MAX); SET @TableName = 'Orders '; SET @OrderID = 10251; SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) + ' WHERE OrderID = '+CAST(@OrderID AS VARCHAR(50)) + ' ORDER BY ORDERID DESC' EXEC sp_executesql @sql 注意最后 行；事实证明可以运行； sp_executesql提供接口 sp_executesql命令比EXEC命令更灵活  它提供个接口该接口及支持输入参数也支持输出参数这功能使你可以创建带参数查询串这样就可以比EXEC更好重用执行计划,sp_executesql构成和存储过程非常相似区别的处在于你是动态构建代码它构成包括:代码快参数声明部分参数赋值部分说了这么多还是看看它语法吧EXEC sp_executesql @stmt = <statement>,--类似存储过程主体 @params = <params>, --类似存储过程参数部分 <params assignment> --类似存储过程 @stmt参数是输入 动态批处理它可以引入输入参数或输出参数和存储过程主体语句样只不过它是动态而存储过程是静态不过你也可以在存储过程中使用sp_executesql;@params参数和定义输入/输出参数 存储过程头类似实际上和存储过程头语法完全样；@<params assignment> 和 存储过程EXEC部分类似为了介绍说明sp_executesql对执行计划 管理优于EXEC,我将使用前面讨论EXEC时用到代码1: DECLARE @TableName VARCHAR(50),@sql NVARCHAR(MAX),@OrderID INT; 2: SET @TableName = 'Orders '; 3: SET @OrderID = 10251; 4: SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) + ' WHERE OrderID = @OID ORDER BY ORDERID DESC' 5: EXEC sp_executesql 6: @stmt = @sql, 7: @params = N'@OID AS INT ', 8: @OID = @OrderID 在 该代码和检查它生成执行计划前先清空缓存Cache中执行计划；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 @stmt = @sql, @params = N'@p AS INT OUTPUT', @p = @i OUTPUTSELECT @i该代码返回输出10 以上就是EXEC和sp_executesql 主要区别如果各位看官觉得哪不对或者表达不清楚还请多多指出^_^0
相关文章读者评论发表评论 |
 |
专注于互联网--专注于架构 |
最新标签 网站地图 文章索引 Rss订阅 |
