分页存储过程

自己编写的一个分页存储过程,非常好用,贡献给大家!
CREATE PROCEDURE dbo.GetPageGoods
(
@CurrentPage int=null,--为PageIndex
@PageSize int=null,
@MoveToID int=null,
@MoveRecords int=null,
@RowsCount int=0 output,
@PageCount int=0 output
)
as
--设置PageCount
set @RowsCount=(select count(*) from Goods)
if(@RowsCount%@PageSize=0)
 set @PageCount=(@RowsCount/@PageSize)
else
 set @PageCount=(@RowsCount/@PageSize+1)

--@CurrentPage和@PageSize是传入参数
Set @MoveRecords=@CurrentPage * @PageSize+1

if(@PageCount>=@CurrentPage+1)
begin
 --下面两行实现快速滚动到我们要取的数据的行，并把ID记录下来
 Set Rowcount @MoveRecords
 Select @MoveToID=GoodsID from Goods Order by GoodsID desc
 Set Rowcount @PageSize
 --也可使用top,不过麻烦了些
 Select * From Goods Where GoodsID<=@MoveToID Order By GoodsID desc
--Set Rowcount 0--清除行数设置
end
return @@rowcount
GO

转载于:https://www.cnblogs.com/zhmore/archive/2008/05/15/1198386.html