asp存储过程使用大全

一、先介绍一下什么是存储过程

存储过程是利用SQL Server所提供的Tranact-SQL语言所编写的程序。Tranact-SQL语言是SQL Server提供专为设计数据库应用程序的语言，它是应用程序和SQL Server数据库间的主要程序式设计界面。它好比Oracle数据库系统中的Pro-SQL和Informix的数据库系统能够中的Informix-4GL语言一样。这类语言主要提供以下功能，让用户可以设计出符合引用需求的程序：
1)、变量说明
2)、ANSI兼容的SQL命令(如Select,Update….)
3)、一般流程控制命令(if…else…、while….)
4)、内部函数

二、存储过程的书写格

CREATE PROCEDURE [拥有者.]存储过程名[;程序编号]
[(参数#1,…参数#1024)]
[WITH
{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}
]
[FOR REPLICATION]
AS 程序行

其中存储过程名不能超过128个字。每个存储过程中最多设定1024个参数
(SQL Server 7.0以上版本),参数的使用方法如下:

@参数名 数据类型 [VARYING] [=内定值] [OUTPUT]

每个参数名前要有一个“@”符号,每一个存储过程的参数仅为该程序内部使用,参数的类型除了IMAGE外，其他SQL Server所支持的数据类型都可使用。
[=内定值]相当于我们在建立数据库时设定一个字段的默认值，这里是为这个参数设定默认值。[OUTPUT]是用来指定该参数是既有输入又有输出值的，也就是在调用了这个存储过程时，如果所指定的参数值是我们需要输入的参数，同时也需要在结果中输出的，则该项必须为OUTPUT，而如果只是做输出参数用，可以用CURSOR，同时在使用该参数时，必须指定VARYING和OUTPUT这两个语句。

例子:
CREATE PROCEDURE order_tot_amt @o_id int,@p_tot int output
AS
SELECT @p_tot = sum(Unitprice*Quantity)
FROM orderdetails
WHERE ordered=@o_id

例子说明:
该例子是建立一个简单的存储过程order_tot_amt,这个存储过程根据用户输入的定单ID号码(@o_id),由定单明细表(orderdetails)中计算该定单销售总额[单价(Unitprice)*数量(Quantity)],这一金额通过@p_tot这一参数输出给调用这一存储过程的程序

三、在SQL Server中执行存储过程

在SQL Server的查询分析器中，输入以下代码:
declare @tot_amt int
execute order_tot_amt 1,@tot_amt output
select @tot_amt

以上代码是执行order_tot_amt这一存储过程，以计算出定单编号为1的定单销售金额，我们定义@tot_amt为输出参数，用来承接我们所要的结果

四、在ASP中调用存储过程

<!--  必须加载adovbs.inc文件，否则将出错  -->
<!--#include file="adovbs.inc"-->
<%
dim objCnn
dim objCmd
dim Rs
const o_id=112

’-----建立Connection对象----------
set objCnn=Server.CreateObject("Adodb.connection")
objCnn.Open "driver={sql server};server=localhost;uid=sa;pwd=cncanet;database=check;"
’-----建立Command对象-----------
set objCmd=Server.CreateObject("Adodb.Command")
objCmd.ActiveConnection=objCnn
objCmd.CommandText="order_tot_amt" ’指定存储过程名称
objCmd.CommandType=adCmdStoredProc ’其为Stored Procedure
’-----准备stored procedure 的参数-------
objCmd.Parameters.Append _
objCmd.CreateParameter("o_id",adInteger,adParamInput,,o_id)
objCmd.Parameters.Append _
objCmd.CreateParameter("p_tot",adBigInt,adParamOutput,,0)
’-----执行存储过程----------------------
objCmd.Execute

’-----输出参数以及处理结果--------------
for each parm in objCmd.Parameters
Response.Write parm.name &"="& trim(parm) &"<br>"
next
%>

Command.CreateParameter (Name,[Type],[Direction],[Size],[Value])  
   
'   Name:参数名  
'   Type:参数的数据类型  
'   Direction:参数的方向，指明是参数向存储过程提供信息还是存储过程向ADO返回信息  
                       
'   Size:参数长度  
'   Value:参数的值

参许参数值的类型的意义如下：

名称值 整数值 功能

adDBTimeStamp 135 日期时间数据类型
adDecimal 14  十进制整数值
adDouble 5  双精度小数值
adError 10  系统错误信息
AdGUID 72  全域性唯一识别字(Globally unique identifier)
adDispath 9  COM/OLE自动对象(Automation Object)
adInteger 3  4字节有符号整数
adIUnknown 13  COM/OLE对象
adLongVarBinary 205  大型２字节值
adLongVarChar 201  大型字符串值
adLongVarWChar 203  大型未编码字符串
adNumeric 131  十进制整数值
adSingle 4  单精度浮点小数
adSmallInt 2  2字节有符号整数
adTinyInt 16  1字节有符号整数
adUnsignedBigInt 21  8字节无符号整数
adUnsignedInt 19  4字节无符号整数
adUnsignedSmallInt 18  2字节无符号整数
adUnsignedTinyInt 17  1字节无符号整数
adUserDefined 132  用户自定义数据类型
adVariant 12  OLE对象
adVarBinary 204  双字节字符变量值
adVarChar 200  字符变量值
advarchar 202  未编码字符串变量值
adWchar 130  未编码字符串

方向值的意义如下：
名称值 整数值 功能

adParamInput 1 允许数据输入至该参数当中
adParamOutput 2 允许数据输出至该参数当中
adParamInputOutput 3 允许数据输入、输出至该参数当中
adparamReturnValue 4 允许从一子程序中返回数据至该参数当中

 

1，调用没有参数的存储过程
<%
set conn=server.CreateObject("adodb.connection")
set cmd=server.CreateObject("adodb.command")
strconn="dsn=pubs;uid=sa;pwd"

conn.Open strconn
set cmd.ActiveConnection=conn
cmd.CommandText="{call nono}"
'set rs=cmc.exe 或者cmd.execute
set rs=cmd.Execute()

%>
2，一个输入的参数的存储过程
<%
set conn=server.CreateObject("adodb.connection")
set cmd=server.CreateObject("adodb.command")
strconn="dsn=pubs;uid=sa;pwd"

conn.Open strconn
set cmd.ActiveConnection=conn

cmd.CommandText="{call oneinput(?)}"
cmd.Parameters.Append cmd.CreateParameter("@aaa",adInteger ,adParamInput )
cmd("@aaa")=100

cmd.Execute()

%>
3，一个输入参数和一个输出的参数
<%
set conn=server.CreateObject("adodb.connection")
set cmd=server.CreateObject("adodb.command")
strconn="dsn=pubs;uid=sa;pwd"

conn.Open strconn
set cmd.ActiveConnection=conn

cmd.CommandText = "{call oneinout(?,?)}"
cmd.Parameters.Append cmd.CreateParameter("@aaa",adInteger,adParamInput)
cmd("@aaa")=10
cmd.Parameters.Append cmd.CreateParameter("@bbb",adInteger,adParamOutput)

cmd.Execute()

bbb=cmd("@bbb")
%>
4，一个输入参数，一个输出参数，和一个返回值
<%
set conn=server.CreateObject("adodb.connection")
set cmd=server.CreateObject("adodb.command")
strconn="dsn=pubs;uid=sa;pwd"

conn.Open strconn
set cmd.ActiveConnection=conn

cmd.CommandText="{?=call onereturn(?,?)}"

cmd.Parameters.Append cmd.CreateParameter("@return_value",adInteger,adParamReturnValue )
cmd.Parameters.Append cmd.CreateParameter("@aaa",adInteger,adParamInput )
cmd("@aaa")=10
cmd.Parameters.Append cmd.CreateParameter("@bbb",adInteger,adParamOutput)

cmd.Execute()

bbb=cmd("@bbb")
rrr=cmd("@return_value")
%> 

ASP调用存储过程的技巧

1、最简单的如下
     Dim objConn
     Set objConn = Server.CreateObject("ADOBD.Connection")
     objConn.Open Application("Connection_String")
     'Call the stored procedure to increment a counter on the page
     objConn.Execute "exec sp_AddHit"
    没有参数，没有返回，没有错误处理，就是这个了
    
2、带参数的一种调用
    objConn.Execute "exec sp_AddHit 'http://www.aspalliance.com', 1"
    请注意分割参数，该方法也不返回记录
   
3、返回记录的
     Dim objConn
     Dim objRs
     Set objConn = Server.CreateObject("ADOBD.Connection")
     Set objRs = Server.CreateObject("ADOBD.Recordset")
     objConn.Open Application("Connection_String")
     'Call the stored procedure to increment a counter on the page
     objRs.Open objConn, "exec sp_ListArticles '1/15/2001'"
     
4、……
     Dim objConn
     Dim objCmd
   
    'Instantiate objects
    Set objConn = Server.CreateObject("ADODB.Connection")
    set objCmd = Server.CreateObject("ADODB.Command")
    conn.Open Application("ConnectionString")
   
    With objCmd
     .ActiveConnection = conn 'You can also just specify a connection string here
     .CommandText = "sp_InsertArticle"
     .CommandType = adCmdStoredProc 'Requires the adovbs.inc file or typelib meta tag
    
     'Add Input Parameters
     .Parameters.Append .CreateParameter("@columnist_id", adDouble, adParamInput, , columnist_id)
     .Parameters.Append .CreateParameter("@url", adVarChar, adParamInput, 255, url)
     .Parameters.Append .CreateParameter("@title", adVarChar, adParamInput, 99, url)
     .Parameters.Append .CreateParameter("@description", adLongVarChar, _
     adParamInput, 2147483647, description)
    
     'Add Output Parameters
     .Parameters.Append .CreateParameter("@link_id", adInteger, adParamOutput, , 0)
    
     'Execute the function
     'If not returning a recordset, use the adExecuteNoRecords parameter option
     .Execute, , adExecuteNoRecords
     link_id = .Parameters("@link_id")
    End With
   
5、存储过程的代码
    Create PROCEDURE dbo.sp_InsertArticle
    (
     @columnist_id int,
     @url varchar(255),
     @title varchar(99),
     @description text
     @link_id int OUTPUT
    )
    AS
    BEGIN
     INSERT INTO dbo.t_link (columnist_id,url,title,description)
     VALUES (@columnist_id,@url,@title,@description)
   
     SELECT @link_id = @@IDENTITY
    END

 

ASP调用带参数存储过程的几种方式

1 这也是最简单的方法,两个输入参数，无返回值：
set connection = server.createobject("adodb.connection")
connection.open someDSN
Connection.Execute "procname varvalue1, varvalue2"

''将所有对象清为nothing，释放资源
connection.close
set connection = nothing

2 如果要返回 Recordset 集:
set connection = server.createobject("adodb.connection")
connection.open someDSN
set rs = server.createobject("adodb.recordset")
rs.Open "Exec procname varvalue1, varvalue2",connection

''将所有对象清为nothing，释放资源
rs.close
connection.close
set rs = nothing
set connection = nothing

3 以上两种方法都不能有返回值，（Recordset除外），如果要得到返回值，需要用Command的方法。
首先说明，返回值有两种。一种是在存储过程中直接return一个值，就象C和VB的函数返回值那样；另一种是可以返回多个值，存
储这些值的变量名称需要在调用参数中先行指定。
这个例子要处理多种参数，输入参数，输出参数，返回记录集以及一个直接返回值（够全了吧？）
存储过程如下：

use pubs
GO

-- 建立存储过程
create procedure sp_PubsTest

-- 定义三个参数变量，注意第三个，特别标记是用于输出
@au_lname varchar (20),
@intid int,
@intidout int output

as
select @intidout = @intid + 1

select *
from authors
where au_lname like @au_lname + ''%''

--直接返回一个值
return @intid + 2

调用该存储过程的asp程序如下：

<%@ Language=VBScript %>
<%
Dim CmdSP
Dim adoRS
Dim adCmdSPStoredProc
Dim adParamReturnValue
Dim adParaminput
Dim adParamOutput
Dim adInteger
Dim iVal
Dim oVal
Dim adoField
Dim adVarChar

‘这些值在 VB 中是预定义常量，可以直接调用，但在 VBScript 中没有预定义
adCmdSPStoredProc = 4
adParamReturnValue = 4
adParaminput = 1
adParamOutput = 2
adInteger = 3
adVarChar = 200

iVal = 5
oVal = 3

''建一个command对象
set CmdSP = Server.CreateObject("ADODB.Command")

''建立连结
CmdSP.ActiveConnection = "Driver={SQL Server};server=(local);Uid=sa;Pwd=;Database=Pubs"

''定义command 对象调用名称
CmdSP.CommandText = "sp_PubsTest"

''设置command调用类型是存储过程 (adCmdSPStoredProc = 4)
CmdSP.CommandType = adCmdSPStoredProc

''往command 对象中加参数
''定义存储过程有直接返回值，并且是个整数，省缺值是4
CmdSP.Parameters.Append CmdSP.CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, 4)
''定义一个字符型输入参数
CmdSP.Parameters.Append CmdSP.CreateParameter("@au_lname", adVarChar, adParaminput, 20, "M")
''定义一个整型输入参数
CmdSP.Parameters.Append CmdSP.CreateParameter("@intID", adInteger, adParamInput, , iVal)
''定义一个整型输出参数
CmdSP.Parameters.Append CmdSP.CreateParameter("@intIDOut", adInteger, adParamOutput, oVal)

''运行存储过程，并得到返回记录集
Set adoRS = CmdSP.Execute

''把每个记录打印出来，其中的字段是虚拟的，可以不用管
While Not adoRS.EOF

for each adoField in adoRS.Fields
Response.Write adoField.Name & "=" & adoField.Value & "<br>" & vbCRLF
Next
Response.Write "<br>"
adoRS.MoveNext
Wend

''打印两个输出值：
Response.Write "<p>@intIDOut = “ & CmdSP.Parameters("@intIDOut").Value & "</p>"
Response.Write "<p>Return value = " & CmdSP.Parameters("RETURN_VALUE").Value & "</p>"

''大扫除
Set adoRS = nothing
Set CmdSP.ActiveConnection = nothing
Set CmdSP = nothing
%>

在Asp中使用存储过程

　　为了提高Asp程序的效率，有时需要在Asp中使用使用Sql Server的存储技术，下面简单作一个介绍。存储过程的建立.这里只简单介绍如何在Sql Server的企业管理器中如何建立存储过程：

(１)打开企业管理器Enterprise manager

(２)选择服务器组(SQL Server Group)、服务器、数据库(Database)以及相就的数据库，鼠标右击对应数据库下的Stored Procdures项，在弹出的菜单中选择New Stored Procedure，在Stored Procedures Properties中输入建立存储过程的语句。下面是一个例子：

CREATE PROCEDURE proctest @mycola Char(10),@mycolb Char(10),@mycolc text　
　AS
Insert into chatdata (mycola,mycolb,mycolc) values(@mycola,@mycolb,@mycolc)