asp用存储过程实现快速分页

asp用存储过程实现快速分页
　　在本地测试速度提升了不少。已经加在本站的mtv歌曲列表网页

一、先建立存储过程p_splitpage  

CREATE procedure p_splitpage  
@sql nvarchar(4000),
@currentpage int=2,
@pagesize int=10,
@recordcount int=0 output,
@pagecount int=0 output
 as
set nocount on
declare @p1 int
exec sp_cursoropen @p1 output,@sql,@scrollopt=1,@ccopt=1,@rowcount=@pagecount output
set @recordcount=@pagecount
set @pagecount=ceiling(1.0*@pagecount/@pagesize)  
set @currentpage=(@currentpage-1)*@pagesize+1 
select @recordcount recordcount ,@pagecount  pagecount,@currentpage  currentpage
exec sp_cursorfetch @p1,16,@currentpage,@pagesize
exec sp_cursorclose @p1
set nocount off
GO

二、asp中调用的函数

注：sql为要得到记录集的查询语句,cur_page为当前页码,P_size为每页记录数,R_count,P_count分别为存储过程返回的总记录数和总页数。

<%

Function GetData(Sql,Cur_page,P_size,R_Count,P_Count)

dim rs,Cmd
Set Cmd = Server.CreateObject("ADODB.Command")
Cmd.ActiveConnection=Conn
Cmd.CommandType=4 'adCmdStoredProc
Cmd.prepared=true '保存编译
Cmd.CommandText="p_splitpage" '存储过程 名称
'设置参数
Cmd.Parameters.Append Cmd.CreateParameter("@sql",200,1,500,Sql)
Cmd.Parameters.Append Cmd.CreateParameter("@currentpage",3,1,4,Cur_page)
Cmd.Parameters.Append Cmd.CreateParameter("@pagesize",3,1,4,P_size)
Cmd.Parameters.Append Cmd.CreateParameter("@recordcount",3,2)
Cmd.Parameters.Append Cmd.CreateParameter("@pagecount",3,2)

set  rs=server.CreateObject("adodb.recordset")   
rs.CursorType  =3   
rs.LockType  =3   
rs.CursorLocation  =3   
set  rs.Source  =cmd 
rs.open
set rs=rs.nextrecordset
R_Count=Cmd("@recordcount")
P_Count=Cmd("@pagecount")
set rs=rs.nextrecordset
set GetData=rs
end function%>

三、在asp中调用：

sql="select * from mtv order by id desc"

p_size=30 '这里设定每页显示的记录数  
cur_page=request("intpage")

set rs=GetData(sql,cur_page,p_size,R_Count,P_Count)  

if intpage >= P_Count then
 last=false
end if
rowcount=1
    while not rs.eof
  %>

显示内容

 <%
                   rs.movenext 
                        wend 
                  %>

<table width="99%" border="0" cellpadding="0" cellspacing="0" borderColorLight=#808080 borderColorDark=#ffffff>
          <tr>
            <%if rs.pagecount > 0 then%>
            <td width="32%" align="left">　共有<font color="#ff0000"><%=R_Count%></font>个MTV
              当前页<font color="#ff0000"><%=cur_page%></font>/<%=P_Count%></td>
            <%else%>
            <td width="12%" align="left">当前页0/0</td>
            <%end if%>
            <td width="56%" align="right"><a href="singerlist.asp?id=<%=id%>&page=1">首页</a>|
              <%if pre then%>
              <a href="singerlist.asp?id=<%=id%>&page=<%=intpage -1%>">上页</a>|
              <%end if%>
              <%if last then%>
              <a href="singerlist.asp?id=<%=id%>&page=<%=intpage +1%>">下页</a> |
              <%end if%>
              <a href="singerlist.asp?id=<%=id%>&page=<%=P_count%>">尾页 </a>| 转到第
              <select name="sel_page" onchange="javascript:location=this.options[this.selectedIndex].value;">
                <%
       for i = 1 to P_Count
       if i = intpage then%>
                <option value="singerlist.asp?id=<%=id%>&page=<%=i%>" selected><%=i%></option>
                <%else%>
                <option value="singerlist.asp?id=<%=id%>&page=<%=i%>"><%=i%></option>
                <%
          end if
        next
        %>
              </select>
              页</td>
          </tr>
        </table>