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 ]
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
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
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