使用row_number实现分页的存储过程的简单实现

 

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE   Proc   [dbo].[Pagination]  
  @QueryStr   nvarchar(4000),
  @PageSize   int=10,
  @PageIndex   int=1,
  @FdShow   nvarchar   (4000)='',
  @FdOrder   nvarchar   (1000)=''
 
  as  
    Begin
  
  declare @StartCount varchar(20);
  declare @EndCount varchar(20);
  
  set @StartCount=cast(@PageSize*(@PageIndex-1)+1 as varchar(20));
  set @EndCount=cast(@PageSize*@PageIndex as varchar(20));
   
  if @FdOrder=''
   set @FdOrder='ID';

  exec('select * from (
  select row_number() over (order by '+@FdOrder+') row,'+@FdShow+' from ('+
  ' select top '+@EndCount+' '+@FdShow+' from '+@QueryStr+') tt ) tmp
  where row between '+@StartCount+' and '+ @EndCount);
  return;
 End

GO

 

执行脚本:

exec Pagination 'testTable',5000,1,'*','OrderCol';