简单高效的分页解决方案

    项目需求:数据库有5000条记录，每页显示20条，要求实现每翻一页就读取当前页的20条数据;

    解决方案:根据传入的页码和每页显示的记录数查询出相应的数据

                1>第一页:select Top 10 * from TableName

                2>以后各页:select row_number() over(order by code) rownum,* into #table  from [tableName]                            

               select * from #table where rownum in (@CurrentPageIndex*@PageSize+1,@CurrentPageIndex*@PageSize+10)

                 //注：（@CurrentPageIndex为传入的当前页的页码,@PageSize为每页显示的记录的条数)

                

-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
alter PROCEDURE [dbo].[sp_PageView]
 @strSql nvarchar(1000),
 @FieldKey nvarchar(1000), --用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段
 @PageCurrent int=1,       --要显示的页码
 @PageSize int=10,         --每页的大小(记录数)
 @PageCount int OUTPUT,    --总页数
 @ListCount int OUTPUT     --数据总条数

AS
BEGIN

    IF @PageCount IS NULL OR @PageCount=0
 BEGIN
    declare @sqlListCount nvarchar(2000),@sqlstr nvarchar(2000),@mod int,@except int
    set @sqlListCount=N'select @a=count(1) from ('+@strSql+')t'
    exec sp_executesql @sqlListCount,N'@a int output',@ListCount output

    set @mod   = @ListCount % @PageSize
    set @except= @ListCount / @PageSize
    if @mod=0
       begin
          set @PageCount =@except
       end
    else
       begin
          set @PageCount =@except+1
       end
 END

   set @sqlstr=N'select * from ('
              +N'select row_number() over (order by '+@FieldKey+') rownum,'+substring(ltrim(lower(@strSql)),7,3000)
              +N')z where rownum between '+cast(((@PageCurrent-1)*@PageSize+1) as nvarchar(20))
              +N' and '+cast(@PageCurrent*@PageSize as nvarchar(20))
   exec (@sqlstr)
   --select @ListCount,@PageCount
END