存储过程是一组预先编辑好的SQL语句组成，编译后存储在数据库中。
存储过程可包含程序流、逻辑及对数据库的查询。它们可以接受参数、输出参数、返回单个或者多个结果集及返回值。
存储过程分三大类：用户定义的存储过程、扩展存储过程、系统存储过程。
一、用户定义的存储过程
该种存储过程是指封装了可重用代码的模块或者例程。存储过程可以接受输入参数、向客户端返回表格或者标量结果和消息。调用数据定义语言（DDL）和数据操纵语言（DML）语句，然后返回输出参数。创建自定义存储过程时，存储过程名前加上“##”表示创建了一个全局的临时存储过程；存储过程名前加上“#”，表示创建的局部临时存储过程。局部临时存储过程只能在创建它的回话中使用，会话结束时，将被删除。这两种存储过程都存储在tempdb数据库中。
用户定义的存储过程有两种类型：T-SQL或者CLR.
T-SQL：存储过程是指保存的T-SQL语句集合，可以接受和返回用户提供的参数。例如，存储过程中可能包含根据客户端应用程序提供的信息在一个或者多个表中插入新行所需的语句。存储过程也可能从数据库向客户端应用程序返回数据。
CLR: 存储过程是指引用Microsoft.NET Framework公共语言的方法存储过程，可以接受和返回用户提供的参数，它们在.NET Framework程序集是作为类的公共静态方法实现的。
二、扩展存储过程
扩展存储过程允许使用编程语言（例如C）创建自己的外部例程。扩展存储过程是以在SQL Server环境外执行的动态连接（DLL文件）来实现的，可以加载到SQL Server实例运行的地址空间中执行，扩展存储过程可以用SQL Server扩展存储过程API编程，扩展存储过程以 前缀“xp_”来标识，对于用户来说，扩展存储过程和普通存储过程一样，可以用相同的方法来执行。
三、系统存储过程
系统存储过程是SQL Server系统自身提供的存储过程，可以作为命令执行各种操作。
系统存储过程主要用来从系统表中获取信息，使用系统存储过程完成数据库服务器的管理工作，为系统管理员提供帮助，为用户查看数据库对象提供方便，系统存储过程位于数据库服务器中。例如，sys.sp_changedbowner 就是一个系统存储过程。从物理意义上讲，系统存储过程存储在源数据库中，并以“sp_”开头。从逻辑意义上来讲，系统存储过程出现在每个系统定义数据库和用户定义数据库的sys构架中。可将GRANT、DENY 和REVOKE权限应用于系统存储过程。
图1
图2
注意：虽然SQL Server 2008中的系统存储过程被放在master数据库中，但是仍可以在其他数据库中对其进行调用，而且在调用时不必在存储过程名前加上数据库名。甚至当创建一个新数据库时，一些系统存储过程会在新数据库中被自动创建。
图3
图4
例如：sp_databases --列出当前系统中的所有数据库
图5
例如：sp_renamedb --更改数据库的名称
图6
如图7，将原有的dataBase数据库更改为Hotel数据库。结果如图8.
图7
图8
不带参数，直接写数据库名称，如图9
图9
例如：sp_help --查看表中的所有信息
图10
注意：如图10，我们看到消息版中，告诉我们数据库“master”中不存在或对于此操作无效，发现我们要查询的表不在master数据库中。如图11：
图11
以上是常用的系统存储过程。
接下来，简单说一下扩展存储过程，如图12.通过扩展存储过程在D盘下创建myFile文件夹。
图12
结果如下：
图13
存储过程的特点
接受输入参数并以输出参数的格式向调用过程或者批处理返回多个值。
包含用于在数据库中执行操作（包括调用其他过程）的编程语句。
向调用过程或者批处理返回状态值，以指明成功或者失败（以及失败的原因）。
存储过程的优点：
1、存储过程加快系统运行速度，存储过程在创建时已经被编译，以后每次执行时不需要重新编译。
2、存储过程可以封装复杂的数据库操作，简化操作流程，例如对多个表的更新，删除等。
3、可实现模块化的程序设计，存储过程可以多次调用，提供统一的数据库访问接口，改进应用程序的可维护性。
4、存储过程可以增加代码的安全性，对于用户不能直接操作存储过程中引用的对象，SQL Server可以设定用户对指定存储过程的执行权限。用户只需提交存储过程名称就可以直接执行，避免了攻击者非法截取SQL代码获得用户数据的可能性。
5、存储过程可以降低网络流量，存储过程代码直接存储于数据库中，在客户端与服务器的通信过程中，不会产生大量的T-SQL代码流量。一个需要数百行SQL语句代码的操作可以通过一条执行过程代码来执行，而不需要在网络中发送数百行代码
存储过程的缺点：
1、数据库移植不方便，存储过程依赖与数据库管理系统，SQL Server存储过程中封装的操作代码不能直接移植到其他的数据库管理系统中。
2、不支持面向对象的设计，无法采用面向对象的方式将逻辑业务进行封装，甚至形成通用的可支持服务的业务逻辑框架。
3、代码可读性差，不易维护。不支持集群。
设计存储过程
在创建存储过程之前，必须先设计存储过程。在设计存储过程中，需要了解存储过程的创建规则、存储过程内部名称以及加密过程等相关知识。
一、创建存储过程的规则
（1）CREATE PROCEDURE定义自身可以包括任意数量和类型的SQL语句，但图5中的语句除外。因为不能在存储过程的任何位置使用这些语句。
图14
（2）可以引用在同一存储过程中创建的对象，只要引用时已经创建了该对象即可。
（3）可以在存储过程内引用临时表。
（4）如果在存储过程内创建本地临时表，则临时表仅为该存储过程而存在；退出该存储过程后，临时表将消失。
（5）如果执行的存储过程将调用另一个存储过程，则被调用的存储过程可以访问由第一个存储过程创建的所有对象，包括临时表在内。
（6）如果执行对远程Microsoft SQL Server 2008 实例进行更改的远程存储过程，则不能回滚这些更改。远程存储过程不参与事务处理。
（7）存储过程中的参数的最大数目为2100.
（8）存储过程中的局部变量的最大数目仅受可用内存的限制。
（9）根据可用内存的不同，存储过程最大可达128MB。
二、限定存储过程内的名称
在存储过程内，如果用于语句（例如SELECT、INSERT）的对象名没有限定架构，则架构将默认为该存储过程的架构。在存储过程内，如果创建该存储过程的用户没有限定SELECT、INSERT、UPDATE或者DELETE语句中引用的表名或者视图名，则默认情况下，通过该存储过程对这些表进行的访问将受到该过程创建者的权限的限制。
三、加密存储过程的定义
如果要创建存储过程，并且希望确保其他用户无法查看该过程的定义，则可以使用WITHENCRYPTION子句。这样，过程定义将以不可读的形式存储。
注：存储过程一旦被加密，其定义将无法解密，任何人（包括该存储过程的所有者或者系统管理员）都将无法查看该存储过程的定义。
创建存储过程
创建不带参数的存储过程
CREATE PROC / PROCEDURE 存储过程名 //存储过程名称在架构中必须是唯一的。
AS
SQL语句
GO
图15
创建带参数的存储过程
CREATE PROC / PROCEDURE 存储过程名
@参数1 数据类型=默认值，
.......
@参数n 数据类型=默认值
AS
SQL语句
GO
图16
带参数的存储过程我们创建完成，刷新数据库，就可以看到我们创建好的存储过程。那么我们如何查询执行带参数的存储过程呢？
以上面的为例，第一种查询执行带参数的存储过程：如图一17
图17
第二种方式：如图--18
图18
第三种方式：--如图19
图19
这里我们需要注意的是：
上例结束时间我们设为默认default时，点击执行，消息版中报错。显示必须传递参数3。如果我们前面的参数是以@name=value的形式传递值的，那所有的参数都必须是这样的形式来传递。如图20
图20
一般，操作最简洁明了的是找到创建好的存储过程，单击右键-->执行存储过程
图21
如图22.可以在红框内根据参数，输入想要查询的存储过程信息，点击确定即可。
图22
网友评论