存储过程分页

USE [FRD]
GO
/****** Object:  StoredProcedure [dbo].[ProdunctCount]    Script Date: 06/07/2018 08:43:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[ProdunctCount]
(

@currentpage int,--当前页
@pagesize int, --一页多少行
 @allpage int  output,--共多少页
  @allcount int  output--共多少行
)
as
begin 


select ProIDName,sum(RealNumber)Number,Size,Unit,ProID into #linshi4
from VPurchase_CGOrderDetail
group by ProIDName ,Size,Unit,ProID

 
select top (@pagesize) *
from #linshi4 where ProIDName not IN 
(select top (@pagesize*(@currentpage-1)) ProIDName from #linshi4    )

--取总行数

select @allcount=COUNT(0) from #linshi4	
PRINT '1'
PRINT @allcount
--计算共多少页
set @allpage=ceiling((@allcount/@pagesize))

END