SQL Server分页的存储过程

 

ALTER   Procedure   [ dbo ] . [ QuickPage ]
@strTableName      varchar ( 50 ),     -- 表名
@strFieldList      varchar ( 1000 ),     -- 所要查询的字段序列
@strWhereFilter      varchar ( 1000 ),     -- 查询条件
@strOrderField      varchar ( 1000 ),     -- 排序字段
@strKeyField      varchar ( 50 ),     -- 用来分页的关键字段名
@intPageSize      int ,             -- 每页记录数
@intPageIndex      int ,             -- 当前所要查询的页
@intPageCount      int  output,         -- 总页数
@intRecordCount      int     output         -- 总记录数
as

declare    @sql              nvarchar ( 4000 )     -- 用于构造SQL语句
declare    @beginIndex      int                  -- 起始记录数
declare    @residualNum      int                  -- 余数
begin
-- 构造SQL语句计算总记录数
if   @strWhereFilter   is   null   or   @strWhereFilter   =   ''
     set   @sql = ' SELECT @intRecordCount=count(*) from  '   +   @strTableName
else
     set   @sql = ' SELECT @intRecordCount=count(*) from  '   +   @strTableName   + '  where  '   +   @strWhereFilter    
-- 执行SQL语句计算总记录数，并将其放入@intRecordCount变量中
exec  sp_executesql  @sql ,N ' @intRecordCount int output ' , @intRecordCount  output
-- 计算出总页数
set   @residualNum      =   @intRecordCount   %   @intPageSize
if   @residualNum   =   0
     set   @intPageCount   =   @intRecordCount   /   @intPageSize
else
     set   @intPageCount   =  ( @intRecordCount   /   @intPageSize )  +   1

set   @strFieldList      =   @strFieldList   + ' , ' +   cast ( @intPageCount   as   varchar )  +   '  as PageCount, ' + cast ( @intRecordCount   as   varchar )  + '  as RecordCount '
begin
     if   @intPageIndex   =   1
         begin
             if   @strWhereFilter   is   null   or   @strWhereFilter   =   ''  
                 begin
                     if   @strOrderField   is   null   or   @strOrderField   =   ''
                         set   @sql   =   ' SELECT TOP  ' +   cast ( @intPageSize   as   varchar )  + '   ' +   @strFieldList   + '  FROM  ' +   @strTableName
                     else
                         set   @sql   =   ' SELECT TOP  ' +   cast ( @intPageSize   as   varchar )  + '   ' +   @strFieldList   + '  FROM  ' +   @strTableName   + '  ORDER BY  ' +   @strOrderField
                 end
             else
                 begin
                     if   @strOrderField   is   null   or   @strOrderField   =   ''
                         set   @sql   =   ' SELECT TOP  ' +   cast ( @intPageSize   as   varchar )  + '   ' +   @strFieldList   + '  FROM  ' +   @strTableName   + '  where  ' +   @strWhereFilter
                     else
                         set   @sql   =   ' SELECT TOP  ' +   cast ( @intPageSize   as   varchar )  + '   ' +   @strFieldList   + '  FROM  ' +   @strTableName   + '  where  ' +   @strWhereFilter   + '  ORDER BY  ' + @strOrderField
                 end
         end
     else
         begin
             -- 计算出开始记录位置
             set   @beginIndex          =  ( @intPageIndex   -   1 )  *   @intPageSize  
             if   @strWhereFilter   is   null   or   @strWhereFilter   =   ''  
                 begin
                     if   @strOrderField   is   null   or   @strOrderField   =   ''
                         set   @sql   =   ' SELECT TOP  ' +   cast ( @intPageSize   as   varchar )  + '   ' +   @strFieldList   + '  FROM  ' +   @strTableName   + '  WHERE  ' +   @strKeyField   + '  NOT IN (SELECT TOP  ' +   cast ( @beginIndex   as   varchar )  + '   ' +   @strKeyField   + '  FROM  ' +   @strTableName   + ' ) '
                     else
                         set   @sql   =   ' SELECT TOP  ' +   cast ( @intPageSize   as   varchar )  + '   ' +   @strFieldList   + '  FROM  ' +   @strTableName   + '  WHERE  ' +   @strKeyField   + '  NOT IN (SELECT TOP  ' +   cast ( @beginIndex   as   varchar )  + '   ' +   @strKeyField   + '  FROM  ' +   @strTableName   + '  ORDER BY  ' + @strOrderField + ' ) ORDER BY  ' + @strOrderField
                 end
             else
                 begin
                     if   @strOrderField   is   null   or   @strOrderField   =   ''
                         set   @sql   =   ' SELECT TOP  ' +   cast ( @intPageSize   as   varchar )  + '   ' +   @strFieldList   + '  FROM  ' +   @strTableName   + '  WHERE  ' +   @strKeyField   + '  NOT IN (SELECT TOP  ' +   cast ( @beginIndex   as   varchar )  + '   ' +   @strKeyField   + '  FROM  ' +   @strTableName   + '  where  ' +   @strWhereFilter   + ' ) '
                     else
                         set   @sql   =   ' SELECT TOP  ' +   cast ( @intPageSize   as   varchar )  + '   ' +   @strFieldList   + '  FROM  ' +   @strTableName   + '  WHERE  ' +   @strKeyField   + '  NOT IN (SELECT TOP  ' +   cast ( @beginIndex   as   varchar )  + '   ' +   @strKeyField   + '  FROM  ' +   @strTableName   + '  where  ' +   @strWhereFilter   + '  ORDER BY  ' + @strOrderField + ' ) ORDER BY  ' + @strOrderField
                 end
         end
end
exec ( @sql )  
end

 

 测试

1、生成测试表：

USE   [ SchoolWebData ]
GO
/**/ /****** 对象:  Table [dbo].[TestTable]    脚本日期: 10/31/2006 15:09:46 ******/
SET  ANSI_NULLS  ON
GO
SET  QUOTED_IDENTIFIER  ON
GO
CREATE   TABLE   [ dbo ] . [ TestTable ] (
     [ ID ]   [ int ]   IDENTITY ( 1 , 1 )  NOT   NULL ,
     [ FirstName ]   [ nvarchar ] ( 100 ) COLLATE Chinese_PRC_CI_AS  NULL ,
     [ LastName ]   [ nvarchar ] ( 100 ) COLLATE Chinese_PRC_CI_AS  NULL ,
     [ Country ]   [ nvarchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS  NULL ,
     [ Note ]   [ nvarchar ] ( 2000 ) COLLATE Chinese_PRC_CI_AS  NULL
)  ON   [ PRIMARY ]

2、插入测试记录（20000W条）

SET   IDENTITY_INSERT  TestTable  ON

declare   @i   int
set   @i = 1
while   @i <= 20000
begin
     insert   into  TestTable( [ id ] , FirstName, LastName, Country,Note)  values ( @i ,  ' FirstName_XXX ' , ' LastName_XXX ' , ' Country_XXX ' , ' Note_XXX ' )
     set   @i = @i + 1
end

SET   IDENTITY_INSERT  TestTable  OFF

3、测试存储过程

USE   [ SchoolWebData ]
GO

DECLARE      @return_value   int ,
         @intPageCount   int ,
         @intRecordCount   int

EXEC      @return_value   =   [ dbo ] . [ QuickPage ]
         @strTableName   =  N ' TestTable ' ,
         @strFieldList   =  N ' * ' ,
         @strWhereFilter   =  N ' 0=0 ' ,
         @strOrderField   =  N ' id ' ,
         @strKeyField   =  N ' id ' ,
         @intPageSize   =   15 ,
         @intPageIndex   =   10 ,
         @intPageCount   =   @intPageCount  OUTPUT,
         @intRecordCount   =   @intRecordCount  OUTPUT

SELECT      @intPageCount   as  N ' @intPageCount ' ,
         @intRecordCount   as  N ' @intRecordCount '

SELECT      ' Return Value '   =   @return_value

GO