搜索
首页数据库mysql教程SQLServer : EXEC和sp

SQLServer : EXEC和sp

Jun 07, 2016 pm 03:39 PM
execsqlserver使用摘要

摘要 1,EXEC的使用 2,sp_executesql的使用 MSSQL为我们提供了两种动态执行SQL语句的命令,分别是EXEC和sp_executesql;通常,sp_executesql则更具有优势,它提供了输入输出接口,而EXEC没有。还有一个最大的好处就是利用sp_executesql,能够重用执行计划,这

摘要

1,EXEC的使用

2,sp_executesql的使用

       MSSQL为我们提供了两种动态执行SQL语句的命令,分别是EXEC和sp_executesql;通常,sp_executesql则更具有优势,它提供了输入输出接口,而EXEC没有。还有一个最大的好处就是利用sp_executesql,能够重用执行计划,这就大大提供了执行性能(对于这个我在后面的例子中会详加说明),还可以编写更安全的代码。EXEC在某些情况下会更灵活。除非您有令人信服的理由使用EXEC,否侧尽量使用sp_executesql.

1,EXEC的使用

EXEC命令有两种用法,一种是执行一个存储过程,另一种是执行一个动态的批处理。以下所讲的都是第二种用法。

下面先使用EXEC演示一个例子,代码1

<span>DECLARE</span> @TableName <span>VARCHAR</span>(50),@<span>Sql</span> NVARCHAR(<span>MAX</span>),@OrderID <span>INT</span>;
<span>SET</span> @TableName = <span>'Orders'</span>;
<span>SET</span> @OrderID = 10251;
<span>SET</span> @<span>sql</span> = <span>'SELECT * FROM '</span>+QUOTENAME(@TableName) +<span>'WHERE OrderID = '</span>+<span>CAST</span>(@OrderID <span>AS</span> <span>VARCHAR</span>(10))+<span>' ORDER BY ORDERID DESC'</span>
<span>EXEC</span>(@<span>sql</span>);

注:这里的EXEC括号中只允许包含一个字符串变量,但是可以串联多个变量,如果我们这样写EXEC:

<span>EXEC</span>(<span>'SELECT TOP('</span>+ <span>CAST</span>(@TopCount <span>AS</span> <span>VARCHAR</span>(10)) +<span>')* FROM '</span>+QUOTENAME(@TableName) +<span>' ORDER BY ORDERID DESC'</span>);

 

SQL编译器就会报错,编译不通过,而如果我们这样:

<span>EXEC</span>(@<span>sql</span>+@sql2+@sql3);

编译器就会通过;

 

所以最佳的做法是把代码构造到一个变量中,然后再把该变量作为EXEC命令的输入参数,这样就不会受限制了;

 

EXEC不提供接口

这里的接口是指,它不能执行一个包含一个带变量符的批处理,这里乍一听好像不明白,不要紧,我在下面有一个实例,您一看就知道什么意思.

<span>DECLARE</span> @TableName <span>VARCHAR</span>(50),@<span>Sql</span> NVARCHAR(<span>MAX</span>),@OrderID <span>INT</span>;
<span>SET</span> @TableName = <span>'Orders'</span>;
<span>SET</span> @OrderID = 10251;
<span>SET</span> @<span>sql</span> = <span>'SELECT * FROM '</span>+QUOTENAME(@TableName) +<span>'WHERE <strong>OrderID = @OrderID</strong> ORDER BY ORDERID DESC'</span>
<span>EXEC</span>(@<span>sql</span>);

关键就在SET @sql这一句话中,如果我们运行这个批处理,编译器就会产生一下错误

Msg 137, Level 15, State 2, Line 1
必须声明标量变量 "@OrderID"。

使用EXEC时,如果您想访问变量,必须把变量内容串联到动态构建的代码字符串中,如:<span>SET</span> @<span>sql</span> = <span>'SELECT * FROM '</span>+QUOTENAME(@TableName) +<span>'WHERE OrderID = '</span>+<span>CAST</span>(@OrderID <span>AS</span> <span>VARCHAR</span>(10))+<span>' ORDER BY ORDERID DESC'</span>

串联变量的内容也存在性能方面的弊端。SQL Server为每一个的查询字符串创建新的执行计划,即使查询模式相同也是这样。为演示这一点,先清空缓存中的执行计划

DBCC FREEPROCCACHE (这个不是本文所涉及的内容,您可以查看MS的MSDN)

http://msdn.microsoft.com/zh-cn/library/ms174283.aspx 

将代码1运行3次,分别对@OrderID 赋予下面3个值,10251,10252,10253。然后使用下面的代码查询

<span>SELECT</span> cacheobjtype,objtype,usecounts,<span>sql</span> <span>FROM</span> sys.syscacheobjects <span>WHERE</span> <span>sql</span> <span>NOT</span> <span>LIKE</span> <span>'%cach%'</span> <span>AND</span> <span>sql</span> <span>NOT</span> <span>LIKE</span> <span>'%sys.%'</span> 

点击F5运行,就会出现下面如图所示的查询结果:
SQLServer : EXEC和sp

我们可以看到,每执行一次都要产生一次的编译,执行计划没有得到充分重用。

EXEC除了不支持动态批处理中的输入参数外,他也不支持输出参数。默认情况下,EXEC把查询的输出返回给调用者。例如下面代码返回Orders表中所有的记录数

<span>DECLARE</span> @<span>sql</span> NVARCHAR(<span>MAX</span>)
<span>SET</span> @<span>sql</span> = <span>'SELECT COUNT(ORDERID) FROM Orders'</span>;
<span>EXEC</span>(@<span>sql</span>);
然而,如果你要把输出返回给调用批处理中的变量,事情就没有那么简单了。为此,你必须使用INSERT EXEC语法把输出插入到一个目标表中,然后从这表中获取值后赋给该变量,就像这样:

<span>DECLARE</span> @<span>sql</span> NVARCHAR(<span>MAX</span>),@RecordCount INT
<span>SET</span> @<span>sql</span> = <span>'SELECT COUNT(ORDERID) FROM Orders'</span>;
 
<span>CREATE</span> <span>TABLE</span> #T(TID <span>INT</span>);
INSERT INTO #T <span>EXEC</span>(@<span>sql</span>);
<span>SET</span> @RecordCount = (<span>SELECT</span> TID <span>FROM</span> #T)
<span>SELECT</span> @RecordCount
<span>DROP</span> <span>TABLE</span> #T

2,sp_executesql的使用

sp_executesql命令在SQL Server中引入的比EXEC命令晚一些,它主要为重用执行计划提供更好的支持。

为了和EXEC作一个鲜明的对比,我们看看如果用代码1的代码,把EXEC换成sp_executesql,看看是否得到我们所期望的结果

<span>DECLARE</span> @TableName <span>VARCHAR</span>(50),@<span>sql</span> NVARCHAR(<span>MAX</span>),@OrderID <span>INT</span> ,@sql2 NVARCHAR(<span>MAX</span>);
<span>SET</span> @TableName = <span>'Orders '</span>;
<span>SET</span> @OrderID = 10251;
<span>SET</span> @<span>sql</span> = <span>'SELECT * FROM '</span>+QUOTENAME(@TableName) + <span>' WHERE OrderID = '</span>+<span>CAST</span>(@OrderID <span>AS</span> <span>VARCHAR</span>(50)) + <span>' ORDER BY ORDERID DESC'</span>
<span>EXEC</span> sp_executesql @sql

注意最后一行;

事实证明可以运行;

sp_executesql提供接口

sp_executesql命令比EXEC命令更灵活,因为它提供一个接口,该接口及支持输入参数也支持输出参数。这功能使你可以创建带参数的查询字符串,这样就可以比EXEC更好的重用执行计划,sp_executesql的构成与存储过程非常相似,不同之处在于你是动态构建代码。它的构成包括:代码快,参数声明部分,参数赋值部分。说了这么多,还是看看它的语法吧

EXEC sp_executesql

@stmt = ,--类似存储过程主体

@params = , --类似存储过程参数部分

--类似存储过程调用

@stmt参数是输入的动态批处理,它可以引入输入参数或输出参数,和存储过程的主体语句一样,只不过它是动态的,而存储过程是静态的,不过你也可以在存储过程中使用sp_executesql;

@params参数与定义输入/输出参数的存储过程头类似,实际上和存储过程头的语法完全一样;

@ 与调用存储过程的EXEC部分类似。

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

<span>   1:  </span>DECLARE @TableName VARCHAR(50),@sql NVARCHAR(MAX),@OrderID INT;
<span>   2:  </span>SET @TableName = <span>'Orders '</span>;
<span>   3:  </span>SET @OrderID = 10251;
<span>   4:  </span>SET @sql = <span>'SELECT * FROM '</span>+QUOTENAME(@TableName) + <span>' WHERE OrderID = @OID ORDER BY ORDERID DESC'</span>
<span>   5:  </span>EXEC sp_executesql
<span>   6:  </span>    @stmt = @sql,
<span>   7:  </span>    @<span>params</span> = N<span>'@OID AS INT '</span>,
<span>   8:  </span>    @OID = @OrderID

在调用该代码和检查它生成的执行计划前,先清空缓存中的执行计划;

DBCC FREEPROCCACHE

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

SELECT cacheobjtype,objtype,usecounts,sql FROM sys.syscacheobjects WHERE sql NOT LIKE <span>'%cache%'</span> AND sql NOT LIKE <span>'%sys.%'</span> AND sql NOT LIKE <span>'%sp_executesql%'</span>

点击F5运行,就会出现如下表所示的结果;

SQLServer : EXEC和sp

sq_executesql的另一个与其接口有关的强大功能是,你可以使用输出参数为调用批处理中的 变量返回值。利用该功能可以避免用临时表返回数据,从而得到更高效的代码和更少的重新编译。定义和使用输出参数的语法与存储过程类似。也就是说,你需要在声明参数时指定OUTPUT子句。例如,下面的静态代码简单的演示了如何从动态批处理中利用输出参数@p把值返回到外部批处理中的变量@i.

DECLARE @sql AS NVARCHAR(12),@i AS INT;
SET @sql = N<span>' SET @p = 10'</span>;
EXEC sp_executesql 
    @stmt = @sql,
    @<span>params</span> = N<span>'@p AS INT OUTPUT'</span>,
    @p = @i OUTPUT
SELECT @i

该代码返回输出10

 

          以上就是EXEC和sp_executesql的主要区别,如果各位看官觉得哪不对或者表达不清楚的,还请多多指出^_^SQLServer : EXEC和sp

 

          作者:兴百放

          时间:2008-11-2 22:30

          网址:Http://xbf321.cnblogs.com

 

 

          参考文献: SQL Server 2005 技术内幕-T-SQL 程序设计

声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
MySQL索引基数如何影响查询性能?MySQL索引基数如何影响查询性能?Apr 14, 2025 am 12:18 AM

MySQL索引基数对查询性能有显着影响:1.高基数索引能更有效地缩小数据范围,提高查询效率;2.低基数索引可能导致全表扫描,降低查询性能;3.在联合索引中,应将高基数列放在前面以优化查询。

MySQL:新用户的资源和教程MySQL:新用户的资源和教程Apr 14, 2025 am 12:16 AM

MySQL学习路径包括基础知识、核心概念、使用示例和优化技巧。1)了解表、行、列、SQL查询等基础概念。2)学习MySQL的定义、工作原理和优势。3)掌握基本CRUD操作和高级用法,如索引和存储过程。4)熟悉常见错误调试和性能优化建议,如合理使用索引和优化查询。通过这些步骤,你将全面掌握MySQL的使用和优化。

现实世界Mysql:示例和用例现实世界Mysql:示例和用例Apr 14, 2025 am 12:15 AM

MySQL在现实世界的应用包括基础数据库设计和复杂查询优化。1)基本用法:用于存储和管理用户数据,如插入、查询、更新和删除用户信息。2)高级用法:处理复杂业务逻辑,如电子商务平台的订单和库存管理。3)性能优化:通过合理使用索引、分区表和查询缓存来提升性能。

MySQL中的SQL命令:实践示例MySQL中的SQL命令:实践示例Apr 14, 2025 am 12:09 AM

MySQL中的SQL命令可以分为DDL、DML、DQL、DCL等类别,用于创建、修改、删除数据库和表,插入、更新、删除数据,以及执行复杂的查询操作。1.基本用法包括CREATETABLE创建表、INSERTINTO插入数据和SELECT查询数据。2.高级用法涉及JOIN进行表联接、子查询和GROUPBY进行数据聚合。3.常见错误如语法错误、数据类型不匹配和权限问题可以通过语法检查、数据类型转换和权限管理来调试。4.性能优化建议包括使用索引、避免全表扫描、优化JOIN操作和使用事务来保证数据一致性

InnoDB如何处理酸合规性?InnoDB如何处理酸合规性?Apr 14, 2025 am 12:03 AM

InnoDB通过undolog实现原子性,通过锁机制和MVCC实现一致性和隔离性,通过redolog实现持久性。1)原子性:使用undolog记录原始数据,确保事务可回滚。2)一致性:通过行级锁和MVCC确保数据一致。3)隔离性:支持多种隔离级别,默认使用REPEATABLEREAD。4)持久性:使用redolog记录修改,确保数据持久保存。

MySQL的位置:数据库和编程MySQL的位置:数据库和编程Apr 13, 2025 am 12:18 AM

MySQL在数据库和编程中的地位非常重要,它是一个开源的关系型数据库管理系统,广泛应用于各种应用场景。1)MySQL提供高效的数据存储、组织和检索功能,支持Web、移动和企业级系统。2)它使用客户端-服务器架构,支持多种存储引擎和索引优化。3)基本用法包括创建表和插入数据,高级用法涉及多表JOIN和复杂查询。4)常见问题如SQL语法错误和性能问题可以通过EXPLAIN命令和慢查询日志调试。5)性能优化方法包括合理使用索引、优化查询和使用缓存,最佳实践包括使用事务和PreparedStatemen

MySQL:从小型企业到大型企业MySQL:从小型企业到大型企业Apr 13, 2025 am 12:17 AM

MySQL适合小型和大型企业。1)小型企业可使用MySQL进行基本数据管理,如存储客户信息。2)大型企业可利用MySQL处理海量数据和复杂业务逻辑,优化查询性能和事务处理。

幻影是什么读取的,InnoDB如何阻止它们(下一个键锁定)?幻影是什么读取的,InnoDB如何阻止它们(下一个键锁定)?Apr 13, 2025 am 12:16 AM

InnoDB通过Next-KeyLocking机制有效防止幻读。1)Next-KeyLocking结合行锁和间隙锁,锁定记录及其间隙,防止新记录插入。2)在实际应用中,通过优化查询和调整隔离级别,可以减少锁竞争,提高并发性能。

See all articles

热AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover

AI Clothes Remover

用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool

Undress AI Tool

免费脱衣服图片

Clothoff.io

Clothoff.io

AI脱衣机

AI Hentai Generator

AI Hentai Generator

免费生成ai无尽的。

热门文章

R.E.P.O.能量晶体解释及其做什么(黄色晶体)
3 周前By尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.最佳图形设置
3 周前By尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.如果您听不到任何人,如何修复音频
3 周前By尊渡假赌尊渡假赌尊渡假赌
WWE 2K25:如何解锁Myrise中的所有内容
1 个月前By尊渡假赌尊渡假赌尊渡假赌

热工具

Dreamweaver Mac版

Dreamweaver Mac版

视觉化网页开发工具

安全考试浏览器

安全考试浏览器

Safe Exam Browser是一个安全的浏览器环境,用于安全地进行在线考试。该软件将任何计算机变成一个安全的工作站。它控制对任何实用工具的访问,并防止学生使用未经授权的资源。

WebStorm Mac版

WebStorm Mac版

好用的JavaScript开发工具

mPDF

mPDF

mPDF是一个PHP库,可以从UTF-8编码的HTML生成PDF文件。原作者Ian Back编写mPDF以从他的网站上“即时”输出PDF文件,并处理不同的语言。与原始脚本如HTML2FPDF相比,它的速度较慢,并且在使用Unicode字体时生成的文件较大,但支持CSS样式等,并进行了大量增强。支持几乎所有语言,包括RTL(阿拉伯语和希伯来语)和CJK(中日韩)。支持嵌套的块级元素(如P、DIV),

DVWA

DVWA

Damn Vulnerable Web App (DVWA) 是一个PHP/MySQL的Web应用程序,非常容易受到攻击。它的主要目标是成为安全专业人员在合法环境中测试自己的技能和工具的辅助工具,帮助Web开发人员更好地理解保护Web应用程序的过程,并帮助教师/学生在课堂环境中教授/学习Web应用程序安全。DVWA的目标是通过简单直接的界面练习一些最常见的Web漏洞,难度各不相同。请注意,该软件中