其核心是利用row_no

create PROCEDURE [dbo].[PR_Paging_RowNum]
(
@object varchar(3000),     --对哪个表和视图进行排序如table1
@sort varchar(3000) = NULL,   --要排序的字段 如 order by id desc
@Pageidx int = 1,     
@PageSize int = 20,
@getfields varchar(5000) = '*',--如id,createtime,title.....
@getfields1 varchar(5000) = '*',-getfileds和getfields1一般情况下都是一样的，用来解决多表连接导致的select语法错误
@filter varchar(5000) = NULL--如where createtime>=2007-5-1 0:0:00
)
AS
declare @strsql varchar(5000)
set @strsql='declare @total int '+'select @total=count(*) from '+@object+@filter
if @pageidx=1
  set @strsql=@strsql+' select top '+cast(@pagesize as varchar(10))+' '+@getfields+',@total as totalrecords from '+@object+' '+@filter+@sort
else
 begin
  declare @startrowno varchar(10)
  declare @endrowno varchar(10)
  set @startrowno=cast((@pageidx-1)*@pagesize+1 as varchar(10))
  set @endrowno=cast(@pageidx*@pagesize as varchar(10))
  
  set @strsql=@strsql+';with t0110x as ('+
  'select ROW_NUMBER() OVER('+@sort+') AS rowno,'+@getfields+',@total as totalrecords from '+@object+@filter+
  ')  '+
  'select '+@getfields1+',totalrecords from t0110x where rowno between '+@startrowno+' and '+@endrowno
 end
exec( @strsql)