ASP程序设计：快速创建存储过程

创建存储过程使用的是一种被称为数据定义语言（DDL）的命令，该命令是对SQL的扩展。用于创建存储过程的工具很多，每种大型数据库都带有书写存储过程的工具，如Microsoft的I/SQL 和SQL Server 的企业管理器等。存储过程可以包含多条Transact-SQL 语句，而Transact-SQL对使用Microsoft SQL Server非常重要，与SQL Server 通讯的所有应用程序都通过向服务器发送Transact-SQL语句来进行通讯。

1.创建存储过程的语法

可使用Transact-SQL 语句CREATE PROCEDURE 创建存储过程。创建存储过程前，请考虑下列事项：

● 不能将CREATE PROCEDURE语句与其他SQL语句组合到单个批处理中；

● 创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户；

● 存储过程是数据库对象，其名称必须遵守标识符规则；

● 只能在当前数据库中创建存储过程。

而且，在创建存储过程时，还应指定：

● 所有输入参数和向调用过程或批处理返回的输出参数；

● 执行数据库操作（包括调用其他过程）的编程语句；

● 返回至调用过程或批处理以表明成功或失败（以及失败原因）的状态值。

创建存储过程的语法如下：

下面将按顺序分别介绍各个参数的意义。

其中procedure_name 为新存储过程的名称，过程名必须符合标识符规则，且对于数据库及其所有者必须惟一。

number 是可选的整数，用来对同名的过程分组，以便用一条DROP PROCEDURE 语句即可将同组的过程一起删除。例如，名为orders 的应用程序使用的过程可以命名为orderproc；1、orderproc；2 等。DROP PROCEDURE orderproc 语句将删除整个组。

@parameter 为过程中的参数。在CREATE PROCEDURE 语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。存储过程最多可以有2100 个参数。

data_type 为参数的数据类型。所有数据类型（包括text、ntext 和image）均可以用作存储过程的参数。

VARYING 为指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用于游标参数。

Default 为参数的默认值。如果定义了默认值，不必指定该参数的值即可执行过程。默认值必须是常量或NULL。

OUTPUT为表明参数是返回参数。

N 为表示最多可以指定2100 个参数的占位符。

RECOMPILE 表明SQL Server 不会缓存该过程的计划，该过程将在运行时重新编译。在使用非典型值或临时值而不希望覆盖缓存在内存中的执行计划时，请使用RECOMPILE 选项。

ENCRYPTION 表示SQL Server 加密syscomments 表中包含CREATE PROCEDURE语句文本的条目。使用ENCRYPTION 可防止将过程作为SQL Server 复制的一部分发布。

FOR REPLICATION 指定不能在订阅服务器上执行为复制创建的存储过程。使用FOR REPLICATION 选项创建的存储过程可用作存储过程筛选，且只能在复制过程中执行。本选项不能和WITH RECOMPILE 选项一起使用。

AS 指定过程要执行的操作。

sql_statement 指定过程中要包含的任意数目和类型的Transact-SQL 语句。但有一些限制。

n是表示此过程可以包含多条Transact-SQL 语句的占位符。(www.xing528.com)

例如，下面的代码创建一个最简单的存储过程：

该代码创建一个存储过程，名字为GetAll，意义是查询student 表中的所有数据。

2.在SQL Server中创建存储过程

由于在ASP应用开发中最常用的大型数据库是SQL Sever，这里将介绍SQL Server 2000如何创建存储过程。

注意：SQL Server 一般是安装在NT环境，如Windows NT4.0和Windows 2000等。

在创建存储过程之前，必须先要有一个表，可以建立一个新表，也可以使用SQL Server自带的“导入和导出数据”工具直接把相应的其他类型的表转换为SQL 的表。这里采用“导入和导出数据”工具把前面的数据库dzlt中的表Student直接转换成SQL的表的方法。

下面先介绍如何转换表。从“开始”菜单里，找到“Microsoft SQL Server”程序组，然后点击里面的“企业管理器”，展开“SQL Sever组”的目录树，结果如图7-5所示。

图7-5　SQL Server的企业管理器

从图中可以知道，SQL 服务器取名为“METC-C201”，里面包含“数据库”、“数据转换服务”、“管理”等。为了不破坏其他系统数据库，新建了一个数据库“SSSSS”和一个用户ssss，这里就将把Acess数据库中的表转换为“SSSSS”库中的表，用户ssss 权限设置为管理SSSSS数据库。

选中“SSSSS”数据库，点击鼠标右键，选择“所有任务”中的“导入数据”，就会出现“DTS 导入/导出向导”，利用该向导就可以完成数据库中表的转换。点击“下一步”，出现“选择数据源”的对话框，由于前面已经把dzlt 数据库在ODBC 中配置，因此在“数据源”处选择“其他（ODBC）数据源”，然后在“用户/系统DSN”处找到dzlt 数据库，再在“用户名”处填入用户“sa”，密码为空（同ODBC 中的设置相同）。如图7-6所示。

图7-6　导入数据中的选择数据源

点击“下一步”，出现“选择目的”，目的取默认值“用于SQL Server的Microsoft OLE DB提供程序”，服务器为默认值“METC-C201”，数据库选择“SSSSS”，然后点击“下一步”。弹出“指定表复制或查询”对话框，选择默认值，然后点击“下一步”。弹出“源表和视图”对话框，选择要转换的表，这里选择了“student”表，然后点击“下一步”。弹出“保存、调度和复制包”对话框，选择默认值，然后点击“下一步”，最后出现“正在完成DTS 导入/导出向导”对话框，点击“完成”，就开始进行表的转换。如果转换成功，就会得到转换成功的消息。这样就完成一个数据表的转换，在“SSSSS”数据库中就可以找到“student”表。

下一步，就开始创建存储过程。

从图7-5中，可以看到在SSSSS 数据库中，除了“表”、“视图”、“用户”等，还有“存储过程”。双击“存储过程”，展开SSSSS 数据库，可以看到在“存储过程”，已经有很多系统已经创建的存储过程。

用鼠标右键点击菜单中的“存储过程”，弹出菜单，选择“新建存储过程”，就可以创建存储过程。在“创建存储过程”对话框中填入下面代码：

该存储过程代码主要完成查询地址的功能，带有一个输入参数address（字符串变量）。

注意：创建的存储过程一定要设置权限，使某一个或几个用户拥有使用和执行的权限，否则在调用时就会出错。

最后，介绍一下在SQL Server中使用存储过程。

打开数据库SSSSS 中的表“Student”，在工具栏里找到“显示/隐藏SQL 窗格”按钮，点击，使SQL窗格显示，可以看到当前窗格里有SQL语句。删掉原有的语句，输入下面代码：

就执行刚才创建的“Find”存储过程，输入参数为“11—202”。执行结果如图7-7所示。

图7-7　存储过程在SQL Server的执行