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 --总记录数asdeclare @sql nvarchar(4000) --用于构造SQL语句declare @beginIndex int --起始记录数declare @residualNum int --余数begin--构造SQL语句计算总记录数if @strWhereFilter is null or @strWhereFilter = '' set @sql='SELECT @intRecordCount=count(*) from ' + @strTableNameelse set @sql='SELECT @intRecordCount=count(*) from ' + @strTableName +' where ' + @strWhereFilter --执行SQL语句计算总记录数，并将其放入@intRecordCount变量中exec sp_executesql @sql,N'@intRecordCount int output',@intRecordCount output--计算出总页数set @residualNum = @intRecordCount % @intPageSizeif @residualNum = 0 set @intPageCount = @intRecordCount / @intPageSizeelse set @intPageCount = (@intRecordCount / @intPageSize) + 1set @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 endendexec(@sql) end
测试
1、生成测试表：
USE [SchoolWebData]GO
/**//****** 对象: Table [dbo].[TestTable] 脚本日期: 10/31/2006 15:09:46 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONdeclare @i intset @i=1while @i<=20000begin insert into TestTable([id], FirstName, LastName, Country,Note) values(@i, 'FirstName_XXX','LastName_XXX','Country_XXX','Note_XXX') set @i=@i+1endSET IDENTITY_INSERT TestTable OFF3、测试存储过程
USE [SchoolWebData]GODECLARE @return_value int, @intPageCount int, @intRecordCount intEXEC @return_value = [dbo].[QuickPage] @strTableName = N'TestTable', @strFieldList = N'*', @strWhereFilter = N'0=0', @strOrderField = N'id', @strKeyField = N'id',