存储过程分页实例(含有分页代码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>&nbsp;&nbsp;" str_html=str_html & "<a href="""&pagename&"&page="&page-1&""">上一页</a>&nbsp;&nbsp;" else str_html=str_html & "首页&nbsp;&nbsp;上一页&nbsp;&nbsp;" end if if cint(page)<cint(spagecount) then str_html=str_html & "<a href="""&pagename&"&page="&page+1&""">下一页</a>&nbsp;&nbsp;" str_html=str_html & "<a href="""&pagename&"&page="&spagecount&""">末页</a>&nbsp;&nbsp;" else str_html=str_html & "下一页&nbsp;&nbsp;末页&nbsp;&nbsp;" end if str_html=str_html & "共"&sRecordCount&"条记录,分"&spagecount&"页显示,当前第"&page&"页&nbsp;&nbsp;" 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 %>

相关内容推荐

html中用js调用ASP文件,实现静态页面动态显示

html中用js调用ASP文件,实现静态页面动态显示,比如HMTL文章的动态新闻评论等 ---简单版[调用代码,把他放哪儿,就在哪儿显示]-------- script language="javascript" src="asdf.asp"/script-----asdf.asp文

用ASP生成Excel文件

用asp生成Excel文件不是像我认为的那么容易。我想这小段代码应该能帮助大家找到输出数据到csv和xls的方法。 %@LANGUAGE="VBSCRIPT" CODEPAGE="936"%meta http-equiv="Content-Type" content="text/html; charset=

获得当前页面地址和来路地址

当前页面地址: html="http://" html=htmlRequest.ServerVariables("Server_Name") html=htmlRequest.ServerVariables("URL") ifrequest.ServerVariables("QUERY_STRING")""then html=html"?"Request.ServerVariables("QUERY_STRING") endif session("

实现多条件模糊查询SQL语句

很多网友问到如何写模糊查询语句和多条件查询,这里我整理了一下,假设以姓名、性别、电话号...作为数据库中的字段名。 通常写一个简单的模糊查询的SQL语句格式可以如下例: sq

怎样用ASP压缩文件

多数的时候我们需要压缩文件,保存到特定的位置,或者你可以使用它实现所有你想要的效果。 我们需要服务器支持WScript.Shell-多数的站长都不会允许这样的。 这里你可以获取下载链接

返回
顶部