分页存储过程
自己编写的一个分页存储过程,非常好用,贡献给大家!
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