------------------------------------------------------------ -- --通用的分页存储过程 -- ------------------------------------------------------------- CREATEPROCEDURE[dbo].[PageList] ( @tblNamenvarchar(200), ----要显示的表或多个表的连接 @fldNamenvarchar(500) ='*', ----要显示的字段列表 @pageSizeint=1, ----每页显示的记录个数 @pageint=10, ----要显示那一页的记录 @fldSortnvarchar(200) =null, ----排序字段列表或条件 @Sortbit=0, ----排序方法,0为升序,1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如:' SortA Asc,SortB Desc,SortC ') @strConditionnvarchar(1000) =null, ----查询条件,不需where,以And开始 @IDnvarchar(150), ----主表的主键 @Distbit=0 , ----是否添加查询字段的 DISTINCT 默认0不添加/1添加 @pageCountint=1 output, ----查询结果分页后的总页数 @Countsint=1 output, ----查询到的记录数 @strSqlnvarchar(1000) ='' output -----最后返回的SQL语句 ) AS SET NOCOUNT ON Declare@sqlTmpnvarchar(1000) ----存放动态生成的SQL语句 Declare@strTmpnvarchar(1000) ----存放取得查询结果总数的查询语句 Declare@strIDnvarchar(1000) ----存放取得查询开头或结尾ID的查询语句 Declare@strSortTypenvarchar(10) ----数据排序规则A Declare@strFSortTypenvarchar(10) ----数据排序规则B Declare@SqlSelectnvarchar(50) ----对含有DISTINCT的查询进行SQL构造 Declare@SqlCountsnvarchar(50) ----对含有DISTINCT的总数查询进行SQL构造 if@Dist=0 begin set@SqlSelect='select ' set@SqlCounts='Count(*)' end else begin set@SqlSelect='select distinct ' set@SqlCounts='Count(DISTINCT '+@ID+')' end if@Sort=0 begin set@strFSortType=' ASC ' set@strSortType=' DESC ' end else begin set@strFSortType=' DESC ' set@strSortType=' ASC ' end --------生成查询语句-------- --此处@strTmp为取得查询结果数量的语句 if@strConditionisnullor@strCondition=''--没有设置显示条件 begin set@sqlTmp=@fldName+' From '+@tblName set@strTmp=@SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName set@strID=' From '+@tblName end else begin set@sqlTmp=+@fldName+'From '+@tblName+' where (1>0) '+@strCondition set@strTmp=@SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName+' where (1>0) '+@strCondition set@strID=' From '+@tblName+' where (1>0) '+@strCondition end ----取得查询结果总数量----- exec sp_executesql @strTmp,N'@Counts int out ',@Counts out declare@tmpCountsint if@Counts=0 set@tmpCounts=1 else set@tmpCounts=@Counts --取得分页总数 set@pageCount=(@tmpCounts+@pageSize-1)/@pageSize /**//**//**//**当前页大于总页数 取最后一页**/ if@page>@pageCount set@page=@pageCount --/*-----数据分页2分处理-------*/ declare@pageIndexint--总数/页大小 declare@lastcountint--总数%页大小 set@pageIndex=@tmpCounts/@pageSize set@lastcount=@tmpCounts%@pageSize if@lastcount>0 set@pageIndex=@pageIndex+1 else set@lastcount=@pagesize --//***显示分页 if@strConditionisnullor@strCondition=''--没有设置显示条件 begin if@pageIndex<2or@page<=@pageIndex/2+@pageIndex%2--前半部分数据处理 begin set@strTmp=@SqlSelect+' top '+CAST(@pageSizeasVARCHAR(4))+''+@fldName+' from '+@tblName +' where '+@ID+' not in('+@SqlSelect+' top '+CAST(@pageSize*(@page-1) asVarchar(20)) +''+@ID+' from '+@tblName +' order by '+@fldSort+''+@strFSortType+')' +' order by '+@fldSort+''+@strFSortType end else begin set@page=@pageIndex-@page+1--后半部分数据处理 if@page<=1--最后一页数据显示 set@strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+CAST(@lastcountasVARCHAR(4))+''+@fldName+' from '+@tblName +' order by '+@fldSort+''+@strSortType+') AS TempTB'+' order by '+@fldSort+''+@strFSortType else set@strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+CAST(@pageSizeasVARCHAR(4))+''+@fldName+' from '+@tblName +' where '+@ID+' not in('+@SqlSelect+' top '+CAST(@pageSize*(@page-2)+@lastcountasVarchar(20)) +''+@ID+' from '+@tblName +' order by '+@fldSort+''+@strSortType+')' +' order by '+@fldSort+''+@strSortType+') AS TempTB'+' order by '+@fldSort+''+@strFSortType end end else--有查询条件 begin if@pageIndex<2or@page<=@pageIndex/2+@pageIndex%2--前半部分数据处理 begin set@strTmp=@SqlSelect+' top '+CAST(@pageSizeasVARCHAR(4))+''+@fldName+' from '+@tblName +' where '+@ID+' not in('+@SqlSelect+' top '+CAST(@pageSize*(@page-1) asVarchar(20)) +''+@ID+' from '+@tblName +' Where (1>0) '+@strCondition+' order by '+@fldSort+''+@strFSortType+')' +''+@strCondition+' order by '+@fldSort+''+@strFSortType end else begin set@page=@pageIndex-@page+1--后半部分数据处理 if@page<=1--最后一页数据显示 set@strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+CAST(@lastcountasVARCHAR(4))+''+@fldName+' from '+@tblName +' where (1>0) '+@strCondition+' order by '+@fldSort+''+@strSortType+') AS TempTB'+' order by '+@fldSort+''+@strFSortType else set@strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+CAST(@pageSizeasVARCHAR(4))+''+@fldName+' from '+@tblName +' where '+@ID+' not in('+@SqlSelect+' top '+CAST(@pageSize*(@page-2)+@lastcountasVarchar(20)) +''+@ID+' from '+@tblName +' where (1>0) '+@strCondition+' order by '+@fldSort+''+@strSortType+')' +@strCondition+' order by '+@fldSort+''+@strSortType+') AS TempTB'+' order by '+@fldSort+''+@strFSortType end end ------返回查询结果----- set@strSql=@strTmp exec sp_executesql @strTmp --print @strTmp