CREATE PROCEDURE GeneralPagination
/*
****************************************************************************************************
*** 用于SqlServer2005(及以上)的高效分页存储过程(支持多字段任意排序,不要求排序字段唯一) ***
****************************************************************************************************
*/
@TableNames varchar(200), --
表名(支持多表)
@FieldStr varchar(4000), --字段名(全部字段为*
)
@SqlWhere varchar(4000), --
条件语句(不用加where)
@GroupBy varchar(4000), --
Group语句(不用加Group By)
@OrderBy varchar(4000), --排序字段(必须!
支持多字段，不用加Order By)
@PageSize int, --
每页多少条记录
@PageIndex int, --
指定当前为第几页
@TotalPage int output, --
返回总页数
@TotalRecord int output --
返回总条数
--with encryption --
加密时使用
As
Begin
--
Begin Transaction
If @SqlWhere =
'无'
set @SqlWhere =
null
If @GroupBy =
'无'
set @GroupBy =
null
Declare @Sql nvarchar(4000)
--
计算总记录数
set @Sql =
'select @TotalRecord = count(*) from ' +
@TableNames
If (@SqlWhere !=
'' or @SqlWhere
is not NULL)
set @Sql = @Sql +
' where ' +
@SqlWhere
Exec sp_executesql @Sql,N'@TotalRecord int output',@TotalRecord output --
计算总记录数
--
计算总页数
set @TotalPage=CEILING((@TotalRecord+
0.0)/
@PageSize)
--
处理页数超出范围情况
if @PageIndex <=
0
Set @PageIndex =
1
if @PageIndex >
@TotalPage
Set @PageIndex =
@TotalPage
--
处理开始点和结束点
Declare @StartRecord int
Declare @EndRecord int
set @StartRecord = (@pageIndex-
1)*@PageSize +
1
set @EndRecord = @StartRecord + @PageSize -
1
--
合成sql条件
Declare @TempStr varchar(4000)
If (@SqlWhere !=
'' or @SqlWhere
is not NULL)
set @TempStr =
' where ' +
@SqlWhere
If (@GroupBy !=
'' or @GroupBy
is not NULL)
set @TempStr = @TempStr +
' Group By ' +
@GroupBy
--
如果是第一页
If (@PageIndex =
1)
Begin
set @Sql =
'select top ' + Convert(varchar(
50),@PageSize) +
' row_number() over(order by ' + @OrderBy +
') as rowId,' + @FieldStr +
' from ' +
@TableNames
If (@TempStr !=
'' or @TempStr
is not NULL)
set @Sql = @Sql +
' ' +
@TempStr
End
Else
Begin
set @Sql =
'select row_number() over(order by ' + @OrderBy +
') as rowId,' + @FieldStr +
' from ' +
@TableNames
If (@TempStr !=
'' or @TempStr
is not NULL)
set @Sql = @Sql +
' ' +
@TempStr
set @Sql =
'Select * from (' + @Sql +
') as TempTable where rowId between ' + Convert(varchar(
50),@StartRecord) +
' and ' + Convert(varchar(
50),@EndRecord)
End
--
执行查询
Exec(@Sql)
-- If @@Error <>
0
--
Begin
--
RollBack Transaction
-- Return -
1
--
End
--
Else
--
Commit Transaction
End
GO
--
drop proc GeneralPagination
--exec GeneralPagination
'Goods a, (select ColumnId,ColumnName from Columninfo where ColumnPath like ''%|23|%'') b,GoodsBrand c',
--
'GoodsId,GoodsName,GoodsPrice,GoodsSmallPic,ColumnId,ColumnName,GoBrName,goodsunit',
--
'a.GoodsColumnId=b.ColumnId and a.GoodsBrandId=c.GoBrId and a.goodsupdownshelf = 1 and a.goodsisdel = 0',
--
'无',
'GoodsPrice Asc,a.goodsid desc',
10,
2,
'000'
转载于:https://www.cnblogs.com/Mr0909/archive/2011/07/06/2099642.html
