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'