SQL server分页存储过程

--分页过程
Alter  proc usp_GetStuPage
@pageNow int = 1,--当前页索引
@pageSize int = 10, --每页的容量
@pageCount int Output--一共有多少页
AS
BEGIN

select @pageCount=Ceiling(COUNT(id)*1.0/@pageSize) from Students

 select * from (select *,ROW_NUMBER() OVER(order by id) as 'rownum' from Students) 
as tb where tb.rownum between (@pageNow-1)*@pageSize+1 and @pageNow*@pageSize; --分页查询
END


--测试过程
declare @pageCount int
EXEC usp_GetStuPage 1,101,@pageCount




--测试
declare @pageNow int = 4,
@pageSize int = 20
select * from (select *,ROW_NUMBER() OVER(order by id) as 'rownum' from Students) 
as tb where tb.rownum between (@pageNow-1)*@pageSize+1 and @pageNow*@pageSize; --分页查询


--测试output 的值pageCount

declare @pageCount int ,@pageSize int =100;
select Ceiling(COUNT(id)*1.0/@pageSize) from Students
print @pageCount;
------------------------------
insert into Students(Name,Age,Gender,Address,Phone,Birthday,CardId,CId)
   (select Name,Age,Gender,Address,Phone,Birthday,CardId,CId from Students);