存储过程及ASP调用存储过程实现分页（二）

存储过程及ASP调用存储过程实现分页（二）

三、创建、修改、删除存储过程

有了刚才的准备工作，现在把重点放在说存储过程。

1、创建存储过程

创建存储过程，存储过程是保存起来的可以接受和返回用户提供的参数的 Transact-SQL 语句的集合。

可以创建一个过程供永久使用，或在一个会话中临时使用（局部临时过程），或在所有会话中临时使用（全局临时过程）。

语法：（注：更详细的说明请参考有关书籍或联机从书，按F1）

CREATE PROC [ EDURE ]   procedure_name

    [ { @parameter data_type }

        [ VARYING ] [ = default ] [ OUTPUT ]

    ] [ ,...n ]

[ WITH    { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS

sql_statement [ ...n ]

我们以前学过知道中括号[]的是可选的，所以最简单的可以简化为：

CREATE PROC procName

AS

sql_statement

其中：procName 就是存储过程名，sql_statement 也就是写Transact-SQL 语句用的。

注意：如果用户创建的存储过程与系统存储过程同名，则永远不执行用户创建的存储过程。

语法总是抽象难理解的，我们来个例子看看就容易理解。

①此存储过程的名字是testPROC1，他的功能是从testTable表中查询前50条记录的信息，也就是返回一个记录集。

Use Johnny

go

CREATE PROC testPROC1

AS

SELECT top 50 * FROM testable

这样存储过程就创建好了。

2、修改存储过程。

对已创建的存储过程觉得要修改时只要把CREATE改为ALTER就可以了。如修改①存储过程（其功能是删除testUser表的所以记录）：

Use Johnny

go

ALTER PROC testPROC1

AS

DELETE FROM testUser

3、删除存储过程。

删除存储过程用命令DROP，即：

DROP PROC[DURE]{ procedure }

如删除①存储过程：

DROP PROC testPROC1

这样①存储过程就删除了。

到此，第一个知识点结束。

四、存储过程的类型及ASP调用实例说明

1、 只返回一个记录集的存储过程

此类型就比如①，现在我们创建个如：

①存储过程testPROC1 代码：

USE johnny

GO

CREATE PROC testPROC1

AS

DELETE FROM testUser

执行下。现在我们在ASP中对这个简单的存储过程进行调用：

②ASP代码：新建一个文件（推荐使用Dreamweaver），然后输入如下代码：

<%@LANGUAGE="VBSCRIPT" CODEPAGE="936"%>

<%

dim conn,connstr

on error resume next

set conn=server.CreateObject("adodb.connection")

connstr="Provider=SQLOLEDB;Server=.;Database=johnny;UID=sa;PWD=123"

conn.open connstr

if err then

err.clear

Set conn = Nothing

response.Write("数据库连接错误")

response.End()

end if

dim cmd,rs

set cmd = Server.CreateObject("ADODB.Command")

cmd.ActiveConnection = connstr '数据库连接字串

cmd.CommandText = "dbo.testPROC1"       '存储过程名

cmd.CommandType = 4 '使用的是存储过程

cmd.CommandTimeout = 0   '超时

cmd.Prepared = true      '将SQL命令先行编译

'cmd.Execute()      '如果是没有返回记录集recordset的（如删除等）直接用cmd.Execute()。

set rs=cmd.Execute()    '有返回记录集并赋给rs

set cmd=nothing

'显示数据信息

while not rs.eof

response.Write(rs(0)&rs(1)&rs(2)&rs(3)&"<hr />")

rs.movenext

wend

%>

看下效果：

说明：

注意：红色为连接数据库及创建对象，绿色为存储过程调用，以后存储过程调用时ASP页面都只写出调用的部分绿色代码，红色部分自己再加上去。

connstr="Provider=SQLOLEDB;Server=.;Database=johnny;UID=sa;PWD=123"

是假设你的SQL Server 2000/2005,在本地，数据库为johnny,用户sa，密码123

在以上代码中，CommandType属性表明请求的类型，取值及说明如下：
　　-1 表明CommandText参数的类型无法确定
　　1 表明CommandText是一般的命令类型
　　2 表明CommandText参数是一个存在的表名称
　　4 表明CommandText参数是一个存储过程的名称

另：还可以通过Connection对象或Recordset对象调用存储过程，方法分别如下：

将以上代码从

dim cmd,rs

……

到

set cmd=nothing

替换为：

（用Connection时）

Dim rs

set rs=conn.execute("dbo.testPROC1",0,4) '最后一个参断含义同CommandType

（用Recordset时）

set rs=server.CreateObject("adodb.recordset")

rs.open "dbo.testPROC1",connstr,0,1,4       '最后一个参断含义含义同CommandType

注意：这Connection对象或Recordset对象调用存储过程的不建议用，特别是在有参数传递的情况下。

以上是简单的存储过程及其在ASP中调用的详细操作。是引导大家入门，下面我们来看看稍微复杂点的也是实际应用中常用的存储过程。