引言
本文旨在介绍使用存储过程封装应用程序所需的 TransactSQL (TSQL) 的主要优缺点以便您对如何在您的环境中使用存储过程做出明智的决策对于那些可以利用存储过程的人员本文还介绍了与在 NET 应用程序中使用存储过程相关的工具和最佳做法
在这方面大家的看法并不一致有些人认为业务逻辑只应被编码到中间层或数据库中有些人认为内联查询是唯一的选择有些人则认为存储过程应用于任何情况所有这些方法都具有优点和缺点重要的是应考虑对您的应用程序和环境至关重要的是什么因此让我们了解一下存储过程的概念并考虑使用它们封装 TSQL 代码的某些原因
考虑使用存储过程的理由
也许您曾经在多处编写过使用 SqlCommand 对象的 TSQL但却从未考虑过是否有一个比将它并入数据访问代码更好的位置由于应用程序随着时间的推移增添了一些功能因此其内部可能包含一些复杂的 TSQL 过程代码存储过程为封装此代码提供了一个替换位置
大多数人可能对存储过程已有所了解但对于那些不了解存储过程的人员而言存储过程是指一组作为单个代码单元一起存储于数据库中的 TSQL 语句您可以使用输入参数传入运行时信息并取回作为结果集或输出参数的数据存储过程在首次运行时将被编译这将产生一个执行计划 实际上是 Microsoft® SQL Server&#; 为在存储过程中获取由 TSQL 指定的结果而必须采取的步骤的记录然后执行计划在内存中得到缓存以备以后使用这样会改善存储过程的性能因为 SQL Server 无需为确定如何处理代码而重新分析它而只需引用缓存的计划即可这个缓存的计划一直可用直到 SQL Server 重新启动或直到它由于使用率较低而溢出内存
性能
缓存的执行计划曾使存储过程较之查询更有性能优势但对于 SQL Server 的几个最新版本执行计划已针对所有 TSQL 批处理进行了缓存而不管它们是否在存储过程中因此基于此功能的性能已不再是存储过程的卖点任何使用静态语法且提交频率足以阻止执行计划溢出内存的 TSQL 批处理将会获得同样的性能好处静态部分是关键任何更改即使像添加注释这样无关紧要的更改也将导致无法与缓存的计划相匹配从而将无法重复使用计划
但是当存储过程可以用于降低网络流量时它们仍然能够提供性能好处您只需在网络中发送 EXECUTE stored_proc_name 语句而非整个 TSQL 例程这可以在复杂操作中广泛使用设计良好的存储过程可以将客户端与服务器之间的许多往返过程简化为单个调用
此外使用存储过程使您能够增强对执行计划的重复使用由此可以通过使用远程过程调用 (RPC) 处理服务器上的存储过程而提高性能使用 StoredProcedure 的 SqlCommandCommandType 时存储过程通过 RPC 执行RPC 封装参数和调用服务器端过程的方式使引擎能够轻松地找到匹配的执行计划并只需插入更新的参数值
考虑使用存储过程提高性能时最后要考虑是否要充分利用 TSQL 的优点请考虑要如何处理数据
&#; 是否要使用基于集合的操作或执行 TSQL 中完全支持的其他操作？那么存储过程就是一个选择而内联查询也可以使用
&#; 是否尝试执行基于行的操作或复杂的字符串处理？那么可能要重新考虑在 TSQL 中进行这种处理这不包括使用存储过程至少要到 Yukon 发布并且公共语言运行库 (CLR) 集成可用后才能使用存储过程
可维护性和抽象
要考虑的另一个潜在优势是可维护性理想情况下数据库架构从不更改业务规则不被修改但在现实环境中情况则完全不同既然情况如此那么如果可以修改存储过程以包括新 XY 和 Z 表（为支持新的销售活动而添加了这些表）中的数据而不是在应用程序代码中的某个位置更改此信息则维护对您来说可能比较容易在存储过程中更改此信息使得更新对应用程序而言具有透明性 您仍然返回相同的销售信息即使存储过程的内部实现已经更改更新存储过程通常比更改测试以及重新部署程序集需要较少的时间和精力
另外通过抽象化实现并将此代码保存在存储过程中任何需要访问数据的应用程序均可以获取一致的数据您无需在多个位置维护相同的代码用户便可获取一致的信息
在存储过程中存储 TSQL 的另一个可维护性优点是更好的版本控制您可以对创建和修改存储过程的脚本进行版本控制就像可以对任何其他源代码模块进行版本控制一样通过使用 Microsoft Visual SourceSafe® 或某个其他源代码控制工具您可以轻松地恢复到或引用旧版本的存储过程
在使用存储过程提高可维护性时应值得注意的一点是它们无法阻止您对架构和规则进行所有可能的更改如果更改范围大到需要对输入存储过程的参数进行更改或者要更改由其返回的数据则您仍需要更新程序集中的代码以添加参数更新 GetValue() 调用等等
要注意的另一个问题是由于存储过程将应用程序绑定到 SQL Server因此使用存储过程封装业务逻辑将限制应用程序的可移植性如果应用程序的可移植性在您的环境中非常重要则将业务逻辑封装在不特定于 RDBMS 的中间层中可能是一个更佳的选择
安全性
考虑使用存储过程的最终原因是它们可用于增强安全性
就管理用户对信息的访问而言通过向用户授予对存储过程（而不是基础表）的访问权限它们可以提供对特定数据的访问您可以将存储过程看成是 SQL Server 视图（如果您对它们熟悉的话）除非存储过程接受用户的输入以动态更改显示的数据
存储过程还可以帮助您解决代码安全问题它们可以防止某些类型的 SQL 插入攻击 主要是一些使用运算符（如 AND 或 OR）将命令附加到有效输入参数值的攻击在应用程序受到攻击时存储过程还可以隐藏业务规则的实现这对于将此类信息视为知识产权的公司非常重要
另外使用存储过程使您可以使用 ADONET 中提供的 SqlParameter 类指定存储过程参数的数据类型这为验证用户提供的值类型（作为深层次防御性策略的一部分）提供了一个简单方法在缩小可接受用户输入的范围方面参数在内联查询中与在存储过程中一样有用
使用存储过程增强安全性时值得注意的是糟糕的安全性或编码做法仍然会使您受到攻击对 SQL Server 角色创建和分配如果不加注意将导致人们访问到不应看到的数据同时如果认为使用存储过程便可防止所有 SQL 插入代码攻击（例如将数据操作语言 (DML) 附加到输入参数）后果将是一样的
另外无论 TSQL 位于代码还是位于存储过程中使用参数进行数据类型验证都不是万无一失的所有用户提供的数据（尤其是文本数据）在传递到数据库之前都应受到附加的验证
存储过程对我是否适用？
或许适合吧让我们概括一下它们的优点
&#; 通过降低网络流量提高性能
&#; 提供单点维护
&#; 抽象化业务规则以确保一致性和安全性
&#; 通过将某些形式的攻击降至最低以增强安全性
&#; 支持执行计划重复使用
如果您的环境允许利用存储过程提供的好处（如上所述）强烈建议使用它们对于改进数据在环境中的处理方式而言它们提供了一个很好的工具另一方面如果您的环境中存在可移植性大量使用非 TSQL 友好的进程或者不稳定的数据库架构等削弱这些优点的因素则您可能要考虑其他方法
另一个要注意的事项是机构内部所拥有的 TSQL 专业人员的数量您有足够的 TSQL 知识吗？您愿意学习吗？或者您有 DBA 或合适的人员帮您编写存储过程吗？掌握的 TSQL 知识越多存储过程就会越好维护它们就会越容易例如TSQL 主要用于基于集合的操作而不是基于行的操作依赖于光标（因为它们向您提示数据集）将导致性能降低如果您不太了解 TSQL请将本文作为一次学习机会无论您将它用在何处本文介绍的知识都将改善您的代码
因此如果您认为存储过程会为应用程序增添特殊的效果请继续阅读本文我们将回顾一些简化存储过程使用的工具并了解一些创建存储过程的最佳做法
Visual Studio NET 工具
Microsoft Visual Studio® NET 提供了一些工具使您能够查看和操作 SQL Server 存储过程（以及其他数据库对象）让我们简要介绍一下您可能期望获得的功能
查看存储过程
您可以使用服务器资源管理器查看现有的存储过程查看它们所需的参数或查看它们的内部实现如果您已经连接到安装了 SQL Server 的服务器则可以依次展开ServerNameSQL ServerSQLInstanceNameNorthwind存储过程并展开CustOrdersDetail该资源管理器将显示存储过程所需的任何参数及其返回的任何列如果签出以上任何项的属性便会发现数据类型表示为 ADO 类型框架文档提供了这些类型与 NET 类型之间的简单映射当然在 ADONET 代码中使用参数时可能要将它们的数据类型表示为 SqlDbType 枚举的成员您会看到 NET 类型与 SqlDbType 类型之间的映射
如果双击存储过程Visual Studio 将会在 SQL 编辑器中打开它其中显示了便于查看的颜色编码和所有内容注意它并不显示 CREATE PROCEDURE 语法（该语法实际上在数据库中已存在）由于它假定您要修改存储过程因此提供了 ALTER PROCEDURE 语法
创建和修改存储过程
您具有在相应
