asp存储过程使用

　　1、调用没有参数的存储过程
<%
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中调用SQL存储过程
<%set connection1 = Server.CreateObject(ADODB.Connection)
connection1.open ... 联接
set command1=Server.CreateObject(ADODB.command)
set command1.activeconnection=connection1
command1.commandtype=4
command1.commandtext=sp_1 SP 名
command1.parameters(1)=... 参数值
command1.parameters(2)=...
set recordset1=command1.execute()
%>

　　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 1
请注意分割参数，该方法也不返回记录

3、返回记录的
Dim objConn
Dim objRs
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
Loop through recordset and display each article
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

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

本新闻共2页,当前在第1页12