存储过程分页实例(含有分页代码asp)
'//分页存储过程
set ANSI_NULLS OFFset QUOTED_IDENTIFIER ONGOALTER proc [dbo].[sh_page] @RecordCount int output, @QueryStr nvarchar(100)='table1',--表名、视图名、查询语句 @PageSize int=20, --每页的大小(行数) @PageCurrent int=2, --要显示的页 从1开始 @FdShow nvarchar (1000)='*', --要显示的字段列表 @IdentityStr nvarchar (100)='id', --主键 @WhereStr nvarchar (1000)='1=1', @FdOrder nvarchar(100)='desc' --排序 只能取desc或者asc as declare @sql nvarchar(2000) set @sql = '' if @WhereStr = '' set @WhereStr = '1=1'
if @PageCurrent = 1 begin set @sql = 'select top ' + cast(@PageSize as nvarchar(3)) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' order by ' + @IdentityStr + ' ' + @FdOrder end
else begin if upper(@FdOrder) = 'DESC' begin set @sql = 'select top ' + cast(@PageSize as nvarchar(3)) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' and ' + @IdentityStr + '< ( select min(' + @IdentityStr + ') from (select top ' + cast(@PageSize*(@PageCurrent-1) as nvarchar(10)) + ' ' + @IdentityStr + ' from ' + @QueryStr + ' where ' + @WhereStr + ' order by ' + @IdentityStr + ' desc) as t) order by ' + @IdentityStr + ' desc' end else begin set @sql = 'select top ' + cast(@PageSize as nvarchar(3)) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' and ' + @IdentityStr + '> ( select max(' + @IdentityStr + ') from (select top ' + cast(@PageSize*(@PageCurrent-1) as nvarchar(10)) + ' ' + @IdentityStr + ' from ' + @QueryStr + ' where ' + @WhereStr + ' order by ' + @IdentityStr + ' asc) as t) order by ' + @IdentityStr + ' asc' end end --print @sql execute(@sql)
--if(@RecordCount is null or @RecordCount<=0)begin declare @tsql nvarchar(800) set @tsql=N'select @RecordCount = count(*) from ' + @QueryStr + ' where ' + @WhereStr exec sp_executesql @tsql,N'@RecordCount int output',@RecordCount output --set @Recordcount=778 --print @sql--end
<%PubLic function PageList(pagename,page,spagecount,sRecordCount)'通用分页函数dim str_htmlstr_html="<table width=""100%"" border=""0"" cellspacing=""1"" cellpadding=""0"">" str_html=str_html & "<form name=""pagelistform"" action="""&pagename&""" method=""post"" >" str_html=str_html & "<tr>" str_html=str_html & "<td class=""b-12"">" if page>1 then str_html=str_html & "<a href="""&pagename&"&page=1"">首页</a> " str_html=str_html & "<a href="""&pagename&"&page="&page-1&""">上一页</a> " else str_html=str_html & "首页 上一页 " end if if cint(page)<cint(spagecount) then str_html=str_html & "<a href="""&pagename&"&page="&page+1&""">下一页</a> " str_html=str_html & "<a href="""&pagename&"&page="&spagecount&""">末页</a> " else str_html=str_html & "下一页 末页 " end if str_html=str_html & "共"&sRecordCount&"条记录，分"&spagecount&"页显示，当前第"&page&"页 " str_html=str_html & "转到第<input name=page type=text class=""input1"" value="""&page&""" size=4>页" if cint(page)=1 and cint(page)=cint(spagecount) then str_html=str_html & "<input type=submit name=submit value=GO disabled=true>" else str_html=str_html & "<input type=submit name=submit value=GO>" end if str_html=str_html & "</td>" str_html=str_html & "</tr>" str_html=str_html & "</form>"str_html=str_html & "</table>"PageList= str_htmlend function
Public Function NewsPageList(spagesize,page,strField,stablename,orderName,strwhere,pagename,connstr)'新闻列表函数'spagesize,每页显示新闻数量'page,需显示页码'strField，需显示字段'stablename,数据表名称'ordername，排序字段'strwhere,查询条件'pagename,当前页面名称Dim cmdTemp,sRecordCount,rs'创建数据库连接Set cmdTemp=server.CreateObject("ADODB.Command")cmdTemp.ActiveConnection =connstrcmdTemp.CommandType=4cmdTemp.CommandText ="page"'创建输出参数cmdTemp.Parameters.Append cmdTemp.CreateParameter("@RecordCount",3,2,4)cmdTemp.Parameters.append cmdTemp.CreateParameter("@QueryStr",200,1,40,stablename)cmdTemp.Parameters.append cmdTemp.CreateParameter("@PageSize",3,1,4,spagesize)cmdTemp.Parameters.append cmdTemp.CreateParameter("@PageCurrent",3,1,4,page)cmdTemp.Parameters.append cmdTemp.CreateParameter("@FdShow",200,1,400,strField)cmdTemp.Parameters.append cmdTemp.CreateParameter("@IdentityStr",200,1,40,orderName)cmdTemp.Parameters.append cmdTemp.CreateParameter("@WhereStr",200,1,400,strwhere)set rs=cmdTemp.Executespagecount=0if not rs.eof then do while not rs.eof str_titler=rs("title") '格式化日期 if idate=1 then str_date="["&formatdatetime(rs("AddDate"))&"]" else str_date="" end if str_titler="·<a href=""News_show.asp?Newsid="&rs("articleid")&""" target=""_blank"" class=""b12-h"">"&str_titler&"</a>"&str_date&"<br>"
str_html=str_html & "<table width=""100%"" height=""22"" border=""0"" align=""center"" cellpadding=""0"" cellspacing=""0"">" str_html=str_html & "<tr> " str_html=str_html & "<td class=""b-12""><span class=""b12-h"">" str_html=str_html & str_titler & "</span></td>" str_html=str_html & " </tr>" str_html=str_html & " </table>" str_html=str_html & "<table width=""608"" border=""0"" align=""center"" cellpadding=""0"" cellspacing=""0"" background=""../images/xian.jpg"">" str_html=str_html & " <tr> " str_html=str_html & " <td height=""1""> </tr>" str_html=str_html & "</table>"
rs.movenext loop rs.close set rs=nothing sRecordCount=cmdTemp.Parameters("@RecordCount") spagecount=formatnumber((clng(sRecordCount)/clng(spagesize))+0.5,0)
str_html=str_html & "<table width=""100%"" border=""0"" align=""center"" cellpadding=""0"" cellspacing=""0"">" str_html=str_html & "<tr> " str_html=str_html & "<td class=""b-12"">" & PageList(pagename,page,spagecount,sRecordCount)&" </td>" str_html=str_html & "</tr>" str_html=str_html & "</table>" set cmdTemp=nothing
end if NewsPageList = str_htmlend Function %>
