存储过程分页

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER proc  [dbo].[createPageproc]

@pageIndex int,--当前的页码值

@pageSize int,--每页显示的记录数.(每页显示几行数据)

@rowCount float output,--表示总的记录数

@pageCount float output--表示总页数

as

begin

select @rowCount=count(*) from Classes

select @pageCount=ceiling(@rowCount/@pageSize)--ceiling()近一位取整

if @pageIndex<=1

 begin

  set @pageIndex=1--给变量赋值通过set关键字

  end

if @pageIndex>=@pageCount

  begin

  set @pageIndex=@pageCount

  end

select * from (select row_number() over(order by cid) as num,* from Classes) as temp where num>(@pageIndex-1)*@pageSize and num<=@pageIndex*@pageSize

end