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