SqlServer2008实例51存储过程基础

目录

1.创建基本的存储过程

2.创建带参数的存储过程

3.使用OUTPUT参数

4.修改存储过程

5.删除存储过程

6.在SQL Server启动时自动执行存储过程

7.报告存储过程元数据

8.为存储过程建立文档

---

用存储过程有以下一些好处。

• 存储过程帮助在数据层聚集T-SQL代码。嵌入即席SQL的网站或应用程序在应用环境下很难修改，当即席SQL嵌入在应用程序内的时候，你可能会花费太多时间试图找到和调试嵌入的SQL。一旦找到了bug，你可能就需要重新编译可执行程序，引起不必要的应用程序临时停止或痛苦的应用程序部署。如果把T-SQL集中到存储过程中去，你就只需要集中在一个地方来查找SQL代码或SQL批处理。如果你能正确地为代码建立文档并对代码标准化，存储过程就会提升整个应用程序的可支持性。
• 存储过程帮助大的即席查询减少网络流量。编写应用程序调用而不是500行的SQL调用来执行存储过程，对网络以及应用程序的性能有正面影响，特别是当调用在一分钟内重复数千次时。
• 存储过程促进代码的可复用性。例如，如果你的网站应用程序使用一个下拉菜单来包含一组城市，并且这个下拉菜单用于很多网页，你可以在每个页面调用存储过程而不是在多个地方嵌入相同的SQL。
• 存储过程淡化数据获取的方法。如果你修改了提供源数据的基本表，存储过程（和视图相似）能让应用程序对这个修改透明。这样就不需要修改应用程序底层的代码就能修改。你可以把老的表换成新的，而且只要同样的列和数据类型返回给应用程序，则应用程序完全不知情。
• 与视图不同，存储过程可以利用流控制技术、临时表、表变量等。
• 存储过程对查询响应时间的影响比较稳定。如果你使用大量的即席查询，可能会注意到有时候从查询中返回结果所花的时间变化很大。这可能是由诸如对表（锁）的并发活动或者资源问题(内存、CPU)等外部因素引起的。从另外一面来说，即席查询可能执行得不稳定，因为SQL Server有时候会选择效率较低的执行计划。而存储过程提供了更可靠的查询计划缓存，因此可以重用。即席查询有时候确实比存储过程执行得更好，但是它完全依赖于执行计划被缓存的环境（“被发觉”的参数），以及看你怎么测试、调优以及实现代码了。

    直接访问SQL Server实例和它的数据库的表(更坏的情况是访问sysadmin)会引起安全隐患。内联即席代码特别容易遭受SQL注A (injection)攻击。如果在T-SQL发送到SQL Server实例之前，有害的T-SQL就插入到了既有应用程序的T-SQL代码，那么就会发生SQL注入。除了SQL注入攻击，如果一些人得到了内联代码，他们就能收集数据库中基础架构的信息，从而指导他们尝试攻击。让所有SQL在存储过程内能保证只有存储过程被应用程序引用——而不是每一个列和表名。
    存储过程的另外一个安全方面的好处就是可以授予数据库用户和／或数据库角色对它们的访问而不是授予对表的直接访问权限。存储过程能作为控制层，你可以选择哪些列和行能被存储过程（和调用者）修改，哪些不能。

1.创建基本的存储过程

    存储过程能用于多种不同的活动，包括简单的SELECT、INSERT、UPDATE、DELETE等。T-SQL活动能混合在单个存储过程中，或者可以以模块形式创建存储过程，为每一个或一组任务创建多个存储过程。
    没有参数的存储过程的基本语法如下： 

CREATE PROCEDURE [schema_name.] procedure_name
AS { <sql_statement> [...n] }

    命令的第一个参数是架构和新存储过程的名字。sql_statement参数是存储过程的T-SQL主体。这个参数包含了一个或多个你希望完成的任务。

    示例：创建一个从AdventureWorks数据库查询数据的基本存储过程；

USE AdventureWorks
GO

CREATE PROCEDURE dbo.usp_SEL_ShoppingCardisplay
AS

SELECT sc.ShoppingCartID,
       sc.ShoppingCartItemID,
       sc.Quantity,
       sc.ProductID,
       p.Name ProductName,
       p.ListPrice
FROM Sales.Shoppi