本文章介绍了关于在mmssqlserver中三种分页存储过程实现代码，有需要的朋友可以参考一下下哈，好了费话不说多了我们来看看吧。
--根据max(min)id
 代码如下 复制代码
create proc [dbo].[proc__id]
@pageindex int=1,--当前页数
@pagesize int=10,--每页大小
@tablename varchar(50)='',--表名
@fields varchar(1000)='',--查询的字段集合
@keyid varchar(50)='',--主键
@condition nvarchar(1000)='',--查询条件
@orderstr varchar(500),--排序条件
@totalrecord bigint output--总记录数
as
 if isnull(@orderstr,n'')=n'' set @orderstr=n' order by '+@keyid+n' desc '
 if isnull(@fields,n'')=n'' set @fields=n'*'
 if isnull(@condition,n'')=n'' set @condition=n'1=1'
 declare @sql nvarchar(4000)
 --if(@totalrecord is null)
 --begin
  set @sql=n'select @totalrecord=count(*)'
   +n' from '+@tablename
   +n' where '+@condition
  exec sp_executesql @sql,n'@totalrecord int output',@totalrecord output
 --end
 if(@pageindex=1)
 begin
  set @sql=n'select top '+str(@pagesize)+n' '+@fields+n' from '+@tablename+n' where '+@condition+n' '+@orderstr
  exec(@sql)
 end
 else
 begin
  declare @operatestr char(3),@comparestr char(1)
  set @operatestr='max'
  set @comparestr='>'
  if(@orderstr'')
  begin
   if(charindex('desc',lower(@orderstr))0)
   begin
    set @operatestr='min'
    set @comparestr='   end
  end
  set @sql=n'select top '+str(@pagesize)+n' '+@fields+n' from '+@tablename+n' where '+@keyid+@comparestr
   +n'(select '+@operatestr+n'('+@keyid+n') from '+@tablename+n' where '+@keyid
   +n' in (select top '+str((@pageindex-1)*@pagesize)+n' '+@keyid+n' from '+@tablename+n' where '
   +@condition+n' '+@orderstr+n')) and '+@condition+n' '+@orderstr
  exec(@sql)
 end
go
--根据row_number() over
 代码如下 复制代码
create proc [dbo].[proc_select_page_row]
@pageindex int=1,--当前页数
@pagesize int=10,--每页大小
@tablename varchar(50)='',--表名
@fields varchar(1000)='*',--查询的字段集合
@keyid varchar(50)='',--主键
@condition nvarchar(1000)='',--查询条件
@orderstr varchar(500),--排序条件
@totalrecord bigint  output--总记录数
as
 if isnull(@orderstr,n'')=n'' set @orderstr=n' order by '+@keyid+n' desc '
 if isnull(@fields,n'')=n'' set @fields=n'*'
 if isnull(@condition,n'')=n'' set @condition=n'1=1'
 declare @sql nvarchar(4000)
-- if @totalrecord is null
-- begin
  set @sql=n'select @totalrecord=count(*)'
   +n' from '+@tablename
   +n' where '+@condition
  exec sp_executesql @sql,n'@totalrecord bigint output',@totalrecord output
--end
 if(@pageindex=1)
 begin
  set @sql=n'select top '+str(@pagesize)+n' '+@fields+n' from '+@tablename+n' where '+@condition+n' '+@orderstr
  exec(@sql)
 end
 else
 begin
  declare @startrecord int
  set @startrecord = (@pageindex-1)*@pagesize + 1
  set @sql=n'select * from (select row_number() over ('+ @orderstr +n') as rowid,'+@fields+n' from '+ @tablename+n') as t where rowid>='+str(@startrecord)+n' and rowid  exec(@sql)
 end
go
--根据top id
 代码如下 复制代码
create proc [dbo].[proc_select_page_top]
@pageindex int=1,--当前页数
@pagesize int=10,--每页大小
@tablename varchar(50)='',--表名
@fields varchar(1000)='',--查询的字段集合
@keyid varchar(50)='',--主键
@condition nvarchar(1000)='',--查询条件
@orderstr varchar(500),--排序条件
@totalrecord bigint output--总记录数
as
 if isnull(@orderstr,n'')=n'' set @orderstr=n' order by '+@keyid+n' desc '
 if isnull(@fields,n'')=n'' set @fields=n'*'
 if isnull(@condition,n'')=n'' set @condition=n'1=1'
 declare @sql nvarchar(4000)
 --if(@totalrecord is null)
 --begin
  set @sql=n'select @totalrecord=count(*)'
   +n' from '+@tablename
   +n' where '+@condition
  exec sp_executesql @sql,n'@totalrecord int output',@totalrecord output
 --end
 if(@pageindex=1)
 begin
  set @sql=n'select top '+str(@pagesize)+n' '+@fields+n' from '+@tablename+n' where '+@condition+n' '+@orderstr
  exec(@sql)
 end
 else
 begin
  set @sql=n'select top '+str(@pagesize)+n' '+@fields+n' from '+@tablename+n' where  '+@keyid
  +n' not in(select top '+str((@pageindex-1)*@pagesize)+n' '+@keyid+n' from '
  +@tablename+n' where '+@condition+n' '+@orderstr+n') and '+@condition+n' '+@orderstr
  exec(@sql)
 end
go