分页存储过程的写法 可以被调用

 --每页3条 第一页
select * from 
(select *,row_number() over(order by pid desc) as num from Photos) as t
where num between 1 and 3 order by pid desc 
--每页3条 第二页
select * from 
(select *,row_number() over(order by pid desc) as num from Photos) as t
where num between 4 and 6 order by pid desc 
--每页3条 第三页
select * from 
(select *,row_number() over(order by pid desc) as num from Photos) as t
where num between 7 and 9 order by pid desc 

--第n页
-- pageSize 每页3条  pageIndex当前页码
 select * from 
(select *,row_number() over(order by pid desc) as num from Photos) as t
where num between pageSize*(pageIndex-1) + 1 and pageSize*pageIndex order by pid desc 


--分页存储过程
create proc usp_MyPagedPhotos
	@pageSize int,			--每页几条数据
	@pageIndex int,			--当前页码
	@pageCount int output	--总页数
as
	--总数据条数
	declare @count int
	select @count = count(*) from photos
	
	set @pageCount =  ceiling(@count*1.0/@pageSize)
	
	 select * from 
	(select *,row_number() over(order by pid desc) as num from Photos) as t
	where num between @pageSize*(@pageIndex-1) + 1 and @pageSize*@pageIndex order by pid desc 
	
	
	
	
	
--测试存储过程
declare @n int

exec usp_MyPagedPhotos 3,4,@n output
print @n