SQL 存储过程分页

--存储过程分页方法一
if exists (select * from sys.procedures where name='proc_CommonPage')
   drop procedure proc_CommonPage
go
   create procedure proc_CommonPage  --存储过程名及参数值
   @PageCount int,--每页条数[1-n]
   @PageNow int, --当前页数
   @Count int output --总行数
as
  declare @sql varchar(2000)   --定义sql字符串
  declare @sqlcount varchar(500)
  declare @countTemp int      --Common记录数数
  set @sql='select top '+cast(@PageCount as varchar)+' * from student where no'+' not in
            (select top '+cast(@PageCount*(@Pagenow-1)as varchar)+' no from student)'
  exec(@sql)
  set @sqlcount='select'+cast(@countTemp as varchar)+'=count(1) from student'
  exec(@sqlcount)
  set @Count=@countTemp/@PageCount+1
--测试
declare @xx int
exec proc_CommonPage 5,1,@xx output

--存储过程分页方法二
if exists (select * from sys.procedures where name='proc_CommonPage')
   drop procedure proc_CommonPage
go
  create procedure proc_CommonPage(@pageIndex int, @pageSize int) --页码[0-n]，条数
as
  declare @rowCount int
  select @rowCount=count(1) from Student
  select * from (select row_number() over (order by no)as 'RowIndex',* from Student)as TempTable
  where RowIndex > @pageIndex * @pageSize
  and
  RowIndex <= @pageSize *(@pageIndex+1)
return @rowCount
--测试
exec proc_CommonPage 0,5