首页 / 网页编程 / ASP / MS-SQL分页函数+ASP
MS-SQL分页函数+ASP2010-08-03CREATE PROCEDURE [dbo].[USP_GetList](@pageIndex int = 1 -- 当前页码0 表示返回所有符合内容,@pageSize int = 10 -- 页尺寸,@SID nvarchar(30) = "" -- 主键字段,@strGetField nvarchar(1000) = "*" -- 需要返回的列,@strTableName nvarchar(30) = "" --表名,@strWhere nvarchar(2000) = "" -- 查询条件(注意: 条件中要带where),@strOrderBy nvarchar(300) = "" -- 排序--,@MemberID int=0 --会员ID)ASSET NOCOUNT ONDECLARE @strSQL nvarchar(4000)DECLARE @startPos intDECLARE @endPos intDECLARE @num intSET @startPos=@pageSize*(@pageIndex-1)+1SET @endPos=@startPos+@pageSize-1--页大小*(页数-1)SET @num = @pageSize * (@PageIndex - 1)IF @PageIndex!=0BEGINIF @strWhere != ""SET @strSQL = "SELECT TOP "+ cast(@pageSize as nvarchar(10)) +" "+ @strGetField +" FROM "+ @strTableName +" WHERE ("+ cast(@SID as nvarchar(30)) +" NOT IN (SELECT TOP "+ cast(@num as nvarchar(20)) +" "+ cast(@SID as nvarchar(30)) +" FROM "+ @strTableName +" where "+@strWhere+" ORDER BY "+ cast(@strOrderBy as nvarchar(255)) +" )) and "+@strwhere+" ORDER BY "+cast(@strOrderBy as varchar(255)) + ""elseSET @strSQL = "SELECT TOP "+ cast(@pageSize as nvarchar(10)) +" "+ @strGetField +" FROM "+ @strTableName +" WHERE ("+ cast(@SID as nvarchar(30)) +" NOT IN (SELECT TOP "+ cast(@num as nvarchar(20)) +" "+ cast(@SID as nvarchar(30)) +" FROM "+ @strTableName +" ORDER BY "+ cast(@strOrderBy as nvarchar(255)) +" )) ORDER BY "+ cast(@strOrderBy as varchar(255)) + ""--SET @strSQL = "SELECT TOP 页大小 * FROM Users WHERE (ID NOT IN (SELECT TOP (页大小*(当前页-1)) ID FROM Users ORDER BY ID DESC )) ORDER BY ID DESC"ENDELSEBEGINIF @strWhere != ""SET @strSQL = "select "+ @strGetField +" from " + @strTableName + " where "+ @strWhere +" order by "+ @strOrderByELSESET @strSQL = "select "+ @strGetField +" from " + @strTableName + " order by "+ @strOrderByEND--print(@strSQL)EXEC(@strSQL)GO第二个存储过程CREATE PROCEDURE [dbo].[USP_GetTotal](@strTableName nvarchar(30) = "",@strWhere nvarchar(2000) = "" -- 查询条件(注意: 条件中要带where))ASSET NOCOUNT OFFDECLARE @strSQL nvarchar(2500)IF @strWhere != ""SET @strSQL = "select count(*) as Total from ["+ @strTableName +"] where "+ @strWhereELSESET @strSQL = "select count(*) as Total from ["+ @strTableName +"]"EXEC(@strSQL)GO下面是调用这个存储过程的两个函数。可以放到一个包含文件里,注意,下面的函数里面使用的一些变量,是全局的变量,不是参数传入的,所以需要在调用函数钱,给全局变量赋值。<%"取记录总数存储过程public function getDataRowCount(strTableName, strWhere)dim maxCountdim myobjdim rsCountmaxCount = 0Set myobj = Server.CreateObject("ADODB.Command")with myobj.ActiveConnection = conn.CommandText = "USP_GetTotal".CommandType = 4.Prepared = true.Parameters.append .CreateParameter("@strTableName", 200, 1, 30, strTableName).Parameters.append .CreateParameter("@strWhere", 200, 1, 2000, strWhere)Set rsCount = .Executeend withSet myobj = NothingmaxCount = rsCount("Total")rsCount.close:Set rsCount = NothinggetDataRowCount = maxCountend function"单表分页存储过程sub getDataRS()dim objSet obj = Server.CreateObject("ADODB.Command")with obj.ActiveConnection = conn.CommandText = "USP_GetList".CommandType = 4.Prepared = true.Parameters.append .CreateParameter("@pageIndex", 3, 1, 4, iPageIndex).Parameters.append .CreateParameter("@pageSize", 3, 1, 4, iPageSize).Parameters.append .CreateParameter("@SID", 200, 1, 30, SID) " 2000.Parameters.append .CreateParameter("@strGetField", 200, 1, 1000, strGetField).Parameters.append .CreateParameter("@tableName", 200, 1, 30, strTableName).Parameters.append .CreateParameter("@strWhere", 200, 1, 2000, strWhere).Parameters.append .CreateParameter("@strOrderBy", 200, 1, 300, strOrderBy)Set rstobj = .Executeend withSet obj = Nothingend sub%>下面是调用这组分页存储过程的实例"#############定义分页存储过程所要使用的变量##################################################"1.定义变量,给部分变量设定初始值dim iPageIndex, iPageSize, iRowCountdim strWherem, strTableName, SID, strGetField, strWhere, strOrderByiPageSize = 12 "一页内显示的记录条数strTableName = "dataTable" "表名SID = "id" "主键名strGetField = "id, field1,field2,field3""要显示的字段名strWhere = "where1=1 and where2=2""where子句strOrderBy = "id desc" "排序"2,执行getPageInf() 得到(1)iPageIndex:当前页数,(2)iRowCount:总记录数,(3)iPageCount:总页数Call getPageInfo() "include_gb/page.asp"3,先定义rsTobj,再执行getDataRS().sub getDataRS()使用上面定义的表明,字段,where,页数,总记录数语句等,去访问存储过程,并把结果付给rsTobj(集合)dim rsTobjCall getDataRS()"4,rsTobj得到了返回数据,开始循环输出,写到过程里,方便调用sub showProducts()if rsTobj.eof Thenresponse.Write("暂时没有数据")elsedo while not rsTobj.eofCall showProdTable(rsTobj("id"),rsTobj("NewsName"),rsTobj("Pictures"),rsTobj("abstract"), rsTobj("AddTime"))rsTobj.moveNextloopend ifCloseRS(rsTobj) "关闭链接 include_gb/connSiteData.aspend sub"###############################################################