分页存储过程.docx

1、分页存储过程ALTER PROCEDURE dbo.SP_Page2005TableName varchar(50), -表名Fields varchar(5000) = *, -字段名(全部字段为*)OrderField varchar(5000), -排序字段(必须!支持多字段)sqlWhere varchar(5000) = Null,-条件语句(不用加where)pageSize int, -每页多少条记录pageIndex int = 1 , -指定当前为第几页TotalPage int output -返回总页数asbegin Begin Tran -开始事务 Declare sq

2、l nvarchar(4000); Declare totalRecord int; -计算总记录数 if (SqlWhere= or sqlWhere=NULL) set sql = select totalRecord = count(*) from + TableName else set sql = select totalRecord = count(*) from + TableName + where + sqlWhere EXEC sp_executesql sql,NtotalRecord int OUTPUT,totalRecord OUTPUT-计算总记录数 -计算总页数

3、 select TotalPage=CEILING(totalRecord+0.0)/PageSize) if (SqlWhere= or sqlWhere=NULL) set sql = Select * FROM (select ROW_NUMBER() Over(order by + OrderField + ) as rowId, + Fields + from + TableName else set sql = Select * FROM (select ROW_NUMBER() Over(order by + OrderField + ) as rowId, + Fields +

4、 from + TableName + where + SqlWhere -处理页数超出范围情况 if PageIndexTotalPage Set pageIndex = TotalPage -处理开始点和结束点 Declare StartRecord int Declare EndRecord int set StartRecord = (pageIndex-1)*PageSize + 1 set EndRecord = StartRecord + pageSize - 1 -继续合成sql语句 set Sql = Sql + ) as + TableName + where rowId

5、between + Convert(varchar(50),StartRecord) + and + Convert(varchar(50),EndRecord) Exec(Sql) - If Error 0 Begin RollBack Tran Return -1 End Else Begin Commit Tran Return totalRecord -返回记录总数 End end-参数说明-获取指定页的数据CREATEPROCEDUREpaginationtblNamevarchar(255),-表名strGetFieldsvarchar(1000)=*,-需要返回的列fldName

6、varchar(255)=,-排序的字段名PageSizeint=10,-页尺寸PageIndexint=1,-页码doCountbit=0,-返回记录总数,非0值则返回OrderTypebit=0,-设置排序类型,非0值则降序strWherevarchar(1500)=-查询条件(注意:不要加where)ASdeclarestrSQLvarchar(5000)-主语句declarestrTmpvarchar(110)-临时变量declarestrOrdervarchar(400)-排序类型ifdoCount!=0beginifstrWhere!=setstrSQL=selectcount(*

7、)asTotalfrom+tblName+where+strWhereelsesetstrSQL=selectcount(*)asTotalfrom+tblName+end-以上代码的意思是如果doCount传递过来的不是0，就执行总数统计。以下的所有代码都是doCount为0的情况elsebeginifOrderType!=0beginsetstrTmp=(selectmaxsetstrOrder=orderby+fldName+ascendifPageIndex=1beginifstrWhere!=setstrSQL=selecttop+str(PageSize)+strGetFields

8、+from+tblName+where+strWhere+strOrderelsesetstrSQL=selecttop+str(PageSize)+strGetFields+from+tblName+strOrder-如果是第一页就执行以上代码，这样会加快执行速度endelsebegin-以下代码赋予了strSQL以真正执行的SQL代码setstrSQL=selecttop+str(PageSize)+strGetFields+from+tblName+where+fldName+strTmp+(+fldName+)from(selecttop+str(PageIndex-1)*PageSi

9、ze)+fldName+from+tblName+strOrder+)astblTmp)+strOrderifstrWhere!=setstrSQL=selecttop+str(PageSize)+strGetFields+from+tblName+where+fldName+strTmp+(+fldName+)from(selecttop+str(PageIndex-1)*PageSize)+fldName+from+tblName+where+strWhere+strOrder+)astblTmp)and+strWhere+strOrderendendexec(strSQL)GOCreat

10、ePROCEDUREdbo.proc_ListPage(tblNamenvarchar(200),-要显示的表或多个表的连接fldNamenvarchar(500)=*,-要显示的字段列表pageSizeint=1,-每页显示的记录个数pageint=10,-要显示那一页的记录pageCountint=1output,-查询结果分页后的总页数Countsint=1output,-查询到的记录数fldSortnvarchar(200)=null,-排序字段列表或条件Sortbit=1,-排序方法，0为升序，1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序

11、标记)-程序传参如：SortAAsc,SortBDesc,SortC)strConditionnvarchar(1000)=null,-查询条件,不需whereIDnvarchar(150),-主表的主键Distbit=0-是否添加查询字段的DISTINCT默认0不添加/1添加)ASSETNOCOUNTONDeclaresqlTmpnvarchar(1000)-存放动态生成的SQL语句DeclarestrTmpnvarchar(1000)-存放取得查询结果总数的查询语句DeclarestrIDnvarchar(1000)-存放取得查询开头或结尾ID的查询语句DeclarestrSortType

12、nvarchar(10)-数据排序规则ADeclarestrFSortTypenvarchar(10)-数据排序规则BDeclareSqlSelectnvarchar(50)-对含有DISTINCT的查询进行SQL构造DeclareSqlCountsnvarchar(50)-对含有DISTINCT的总数查询进行SQL构造ifDist=0beginsetSqlSelect=selectsetSqlCounts=Count(*)endelsebeginsetSqlSelect=selectdistinctsetSqlCounts=Count(DISTINCT+ID+)endifSort=0begi

13、nsetstrFSortType=ASCsetstrSortType=DESCendelsebeginsetstrFSortType=DESCsetstrSortType=ASCend-生成查询语句-此处strTmp为取得查询结果数量的语句ifstrConditionisnullorstrCondition=-没有设置显示条件beginsetsqlTmp=fldName+From+tblNamesetstrTmp=SqlSelect+Counts=+SqlCounts+FROM+tblNamesetstrID=From+tblNameendelsebeginsetsqlTmp=+fldName

14、+From+tblName+where(10)+strConditionsetstrTmp=SqlSelect+Counts=+SqlCounts+FROM+tblName+where(10)+strConditionsetstrID=From+tblName+where(10)+strConditionend-取得查询结果总数量-execsp_executesqlstrTmp,NCountsintout,CountsoutdeclaretmpCountsintifCounts=0settmpCounts=1elsesettmpCounts=Counts-取得分页总数setpageCount=

15、(tmpCounts+pageSize-1)/pageSize/*/*当前页大于总页数取最后一页*/ifpagepageCountsetpage=pageCount-/*-数据分页2分处理-*/declarepageIndexint-总数/页大小declarelastcountint-总数%页大小setpageIndex=tmpCounts/pageSizesetlastcount=tmpCounts%pageSizeiflastcount0setpageIndex=pageIndex+1elsesetlastcount=pagesize-/*显示分页ifstrConditionisnullo

16、rstrCondition=-没有设置显示条件beginifpageIndex2orpage=pageIndex/2+pageIndex%2-前半部分数据处理beginsetstrTmp=SqlSelect+top+CAST(pageSizeasVARCHAR(4)+fldName+from+tblName+where+ID+notin(+SqlSelect+top+CAST(pageSize*(page-1)asVarchar(20)+ID+from+tblName+orderby+fldSort+strFSortType+)+orderby+fldSort+strFSortTypeende

17、lsebeginsetpage=pageIndex-page+1-后半部分数据处理ifpage=1-最后一页数据显示setstrTmp=SqlSelect+*from(+SqlSelect+top+CAST(lastcountasVARCHAR(4)+fldName+from+tblName+orderby+fldSort+strSortType+)ASTempTB+orderby+fldSort+strFSortTypeelsesetstrTmp=SqlSelect+*from(+SqlSelect+top+CAST(pageSizeasVARCHAR(4)+fldName+from+tbl

18、Name+where+ID+notin(+SqlSelect+top+CAST(pageSize*(page-2)+lastcountasVarchar(20)+ID+from+tblName+orderby+fldSort+strSortType+)+orderby+fldSort+strSortType+)ASTempTB+orderby+fldSort+strFSortTypeendendelse-有查询条件beginifpageIndex2orpage0)+strCondition+orderby+fldSort+strFSortType+)+strCondition+orderby+

19、fldSort+strFSortTypeendelsebeginsetpage=pageIndex-page+1-后半部分数据处理ifpage0)+strCondition+orderby+fldSort+strSortType+)ASTempTB+orderby+fldSort+strFSortTypeelsesetstrTmp=SqlSelect+*from(+SqlSelect+top+CAST(pageSizeasVARCHAR(4)+fldName+from+tblName+where+ID+notin(+SqlSelect+top+CAST(pageSize*(page-2)+lastcountasVarchar(20)+ID+from+tblName+where(10)+strCondition+orderby+fldSort+strSortType+)+strCondition+orderby+fldSort+strSortType+)ASTempTB+orderby+fldSort+strFSortTypeendend-返回查询结果-execsp_executesqlstrTmp-printstrTmpSETNOCOUNTOFFCreatePROCEDURE dbo.SP_Pagination/*/* 千万数量级分页存储过程 *