分页存储过程.docx
《分页存储过程.docx》由会员分享，可在线阅读，更多相关《分页存储过程.docx（16页珍藏版）》请在冰豆网上搜索。
分页存储过程
ALTERPROCEDURE[dbo].[SP_Page2005]
@TableNamevarchar（50）,--表名
@Fieldsvarchar（5000）='*',--字段名（全部字段为*）
@OrderFieldvarchar（5000）,--排序字段（必须!
支持多字段）
@sqlWherevarchar（5000）=Null,--条件语句（不用加where）
@pageSizeint,--每页多少条记录
@pageIndexint=1,--指定当前为第几页
@TotalPageintoutput--返回总页数
as
begin
BeginTran--开始事务
Declare@sqlnvarchar（4000）;
Declare@totalRecordint;
--计算总记录数
if（@SqlWhere=''or@sqlWhere=NULL）
set@sql='select@totalRecord=count（*）from'+@TableName
else
set@sql='select@totalRecord=count（*）from'+@TableName+'where'+@sqlWhere
EXECsp_executesql@sql,N'@totalRecordintOUTPUT',@totalRecordOUTPUT--计算总记录数
--计算总页数
select@TotalPage=CEILING（（@totalRecord+0.0）/@PageSize）
if（@SqlWhere=''or@sqlWhere=NULL）
set@sql='Select*FROM（selectROW_NUMBER（）Over（orderby'+@OrderField+'）asrowId,'+@Fields+'from'+@TableName
else
set@sql='Select*FROM（selectROW_NUMBER（）Over（orderby'+@OrderField+'）asrowId,'+@Fields+'from'+@TableName+'where'+@SqlWhere
--处理页数超出范围情况
if@PageIndex<=0
Set@pageIndex=1
if@pageIndex>@TotalPage
Set@pageIndex=@TotalPage
--处理开始点和结束点
Declare@StartRecordint
Declare@EndRecordint
set@StartRecord=（@pageIndex-1）*@PageSize+1
set@EndRecord=@StartRecord+@pageSize-1
--继续合成sql语句
set@Sql=@Sql+'）as'+@TableName+'whererowIdbetween'+Convert（varchar（50）,@StartRecord）+'and'+Convert（varchar（50）,@EndRecord）
Exec（@Sql）
---------------------------------------------------
If@@Error<>0
Begin
RollBackTran
Return-1
End
Else
Begin
CommitTran
Return@totalRecord---返回记录总数
End
end
--参数说明
-------------------------------------------------------------
-- 获取指定页的数据
CREATE PROCEDURE pagination
@tblName varchar（255）, -- 表名
@strGetFields varchar（1000） = '*', -- 需要返回的列
@fldName varchar（255）='', -- 排序的字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@doCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar（1500） = '' -- 查询条件 （注意:
不要加 where）
AS
declare @strSQL varchar（5000） -- 主语句
declare @strTmp varchar（110） -- 临时变量
declare @strOrder varchar（400） -- 排序类型
if @doCount !
= 0
begin
if @strWhere !
=''
set @strSQL = "select count（*） as Total from [" + @tblName + "] where "+@strWhere
else
set @strSQL = "select count（*） as Total from [" + @tblName + "]"
end
--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。
以下的所有代码都是@doCount为0的情况
else
begin
if @OrderType !
= 0
begin
set @strTmp = "<（select min"
set @strOrder = " order by [" + @fldName +"] desc"
--如果@OrderType不是0，就执行降序，这句很重要！
end
else
begin
set @strTmp = ">（select max"
set @strOrder = " order by [" + @fldName +"] asc"
end
if @PageIndex = 1
begin
if @strWhere !
= ''
set @strSQL = "select top " + str（@PageSize） +" "+@strGetFields+ " from [" + @tblName + "] where " + @strWhere + " " + @strOrder
else
set @strSQL = "select top " + str（@PageSize） +" "+@strGetFields+ " from ["+ @tblName + "] "+ @strOrder
--如果是第一页就执行以上代码，这样会加快执行速度
end
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
set @strSQL = "select top " + str（@PageSize） +" "+@strGetFields+ " from ["
+ @tblName + "] where [" + @fldName + "]" + @strTmp + "（["+ @fldName + "]） from （select top " + str（（@PageIndex-1）*@PageSize） + " ["+ @fldName + "] from [" + @tblName + "]" + @strOrder + "） as tblTmp）"+ @strOrder
if @strWhere !
= ''
set @strSQL = "select top " + str（@PageSize） +" "+@strGetFields+ " from ["
+ @tblName + "] where [" + @fldName + "]" + @strTmp + "（["
+ @fldName + "]） from （select top " + str（（@PageIndex-1）*@PageSize） + " ["
+ @fldName + "] from [" + @tblName + "] where " + @strWhere + " "
+ @strOrder + "） as tblTmp） and " + @strWhere + " " + @strOrder
end
end
exec （@strSQL）
GO
Create PROCEDURE [dbo].[proc_ListPage]
（
@tblName nvarchar（200）, ----要显示的表或多个表的连接
@fldName nvarchar（500） = '*', ----要显示的字段列表
@pageSize int = 1, ----每页显示的记录个数
@page int = 10, ----要显示那一页的记录
@pageCount int = 1 output, ----查询结果分页后的总页数
@Counts int = 1 output, ----查询到的记录数
@fldSort nvarchar（200） = null, ----排序字段列表或条件
@Sort bit = 1, ----排序方法，0为升序，1为降序（如果是多字段排列Sort指代最后一个排序字段的排列顺序（最后一个排序字段不加排序标记）--程序传参如：
' SortA Asc,SortB Desc,SortC '）
@strCondition nvarchar（1000） = null, ----查询条件,不需where
@ID nvarchar（150）, ----主表的主键
@Dist bit = 0 ----是否添加查询字段的 DISTINCT 默认0不添加/1添加
）
AS
SET NOCOUNT ON
Declare @sqlTmp nvarchar（1000） ----存放动态生成的SQL语句
Declare @strTmp nvarchar（1000） ----存放取得查询结果总数的查询语句
Declare @strID nvarchar（1000） ----存放取得查询开头或结尾ID的查询语句
Declare @strSortType nvarchar（10） ----数据排序规则A
Declare @strFSortType nvarchar（10） ----数据排序规则B
Declare @SqlSelect nvarchar（50） ----对含有DISTINCT的查询进行SQL构造
Declare @SqlCounts nvarchar（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 @strCondition is null or @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 @tmpCounts int
if @Counts = 0
set @tmpCounts = 1
else
set @tmpCounts = @Counts
--取得分页总数
set @pageCount=（@tmpCounts+@pageSize-1）/@pageSize
/**//**当前页大于总页数 取最后一页**/
if @page>@pageCount
set @page=@pageCount
--/*-----数据分页2分处理-------*/
declare @pageIndex int --总数/页大小
declare @lastcount int --总数%页大小
set @pageIndex = @tmpCounts/@pageSize
set @lastcount = @tmpCounts%@pageSize
if @lastcount > 0
set @pageIndex = @pageIndex + 1
else
set @lastcount = @pagesize
--//***显示分页
if @strCondition is null or @strCondition='' --没有设置显示条件
begin
if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理
begin
set @strTmp=@SqlSelect+' top '+ CAST（@pageSize as VARCHAR（4））+' '+ @fldName+' from '+@tblName
+' where '+@ID+' not in（'+ @SqlSelect+' top '+ CAST（@pageSize*（@page-1） as Varchar（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（@lastcount as VARCHAR（4））+' '+ @fldName+' from '+@tblName
+' order by '+ @fldSort +' '+ @strSortType+'） AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
else
set @strTmp=@SqlSelect+' * from （'+@SqlSelect+' top '+ CAST（@pageSize as VARCHAR（4））+' '+ @fldName+' from '+@tblName
+' where '+@ID+' not in（'+ @SqlSelect+' top '+ CAST（@pageSize*（@page-2）+@lastcount as Varchar（20）） +' '+ @ID +' from '+@tblName
+' order by '+ @fldSort +' '+ @strSortType+'）'
+' order by '+ @fldSort +' '+ @strSortType+'） AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
end
end
else --有查询条件
begin
if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理
begin
set @strTmp=@SqlSelect+' top '+ CAST（@pageSize as VARCHAR（4））+' '+ @fldName +' from '+@tblName
+' where '+@ID+' not in（'+ @SqlSelect+' top '+ CAST（@pageSize*（@page-1） as Varchar（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（@lastcount as VARCHAR（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（@pageSize as VARCHAR（4））+' '+ @fldName+' from '+@tblName
+' where '+@ID+' not in（'+ @SqlSelect+' top '+ CAST（@pageSize*（@page-2）+@lastcount as Varchar（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
------返回查询结果-----
exec sp_executesql @strTmp
--print @strTmp
SET NOCOUNT OFF
Create PROCEDURE[dbo].[SP_Pagination]
/**//*
***************************************************************
**千万数量级分页存储过程**
*********************
升级会员 