发布两个大数据集通用分页存储过程
2008年9月6日:
发布两个大数据集通用分页存储过程,欢迎大家和我联系共同探讨改进方法,同时我也会及时将更新内容发布在此页面。
本次一共发布两个版本,分别使用表变量和临时表两种技术实现。各有优缺点,表变量版本的执行效率高,但是需要明确指定“主键列、标识列或具有唯一值的列数据类型”,临时表版本执行效率略低一些。
表变量版本代码:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
/*--用存储过程实现的大数据集分页程序
显示指定查询结果的第X页(使用表变量)
最后修改日期:2008.09.06
Copy Right 2007.09 http://www.thinksea.com (传播时请保留此信息,谢谢!)
欢迎大家来信批评指正,作者的 Email:thinksea@163.com QQ:41464129
--调用示例
1、简单调用示例:
exec SelectPage 'select * from 图书表名称','[id]','uniqueidentifier',5,3
2、对带排序的查询语句有必要时需要使用 top 100 percent。示例如下:
exec SelectPage 'select top 100 percent * from 图书表名称 order by 图书编号','[id]','uniqueidentifier',5,3
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SelectPage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SelectPage]
GO
CREATE PROCEDURE [dbo].[SelectPage]
@QueryString nvarchar(4000), --查询语句
@KeyFieldName nvarchar (250), --主键列、标识列或具有唯一值的列名称
@KeyFieldType nvarchar (250), --主键列、标识列或具有唯一值的列数据类型
@PageIndex int, --指定分页索引,从0开始的整数,0表示第一页
@PageSize int --每页的大小(每页最多显示的记录数)
as
SET @KeyFieldName=N' '+@KeyFieldName
SET @KeyFieldType=N' '+@KeyFieldType
SET @QueryString=N' ('+@QueryString+N') a'
declare @sql nvarchar(4000)
declare @TempFieldName nvarchar(50)--临时字段名
set @TempFieldName=N' [ID_'+cast(newid() as varchar(40))+N']'
set @sql=N'declare @tb1 table(
'+@TempFieldName+N' int IDENTITY(1,1),
'+@KeyFieldName+@KeyFieldType+N'
)
INSERT INTO @tb1 SELECT'+@KeyFieldName+N' from'+@QueryString+N'
select * from'+@QueryString+N' where'+@KeyFieldName+N' in(
select'+@KeyFieldName+N' from @tb1 where' + @TempFieldName + N'>'+cast(@PageIndex*@PageSize as nvarchar(10))+N' and '+@TempFieldName+N'<='+cast(@PageIndex*@PageSize+@PageSize as nvarchar(10))+N'
)
declare @RecordsCount int,
@PagesCount int
select @RecordsCount=count(*) from @tb1
set @PagesCount=ceiling((@RecordsCount+0.0)/'+cast(@PageSize as nvarchar(10))+N')
select @RecordsCount as [RecordsCount], @PagesCount as [PageCount]
'
EXEC ( @sql )
GO
/*--用存储过程实现的大数据集分页程序
显示指定查询结果的从第X条记录开始的N条记录(使用表变量)
最后修改日期:2008.09.06
Copy Right 2007.09 http://www.thinksea.com (传播时请保留此信息,谢谢!)
欢迎大家来信批评指正,作者的 Email:thinksea@163.com QQ:41464129
--调用示例
1、简单调用示例:
exec SelectRange '图书表名称','[id]',5,3,'图书编号,图书名称','图书编号'
2、对带排序的查询语句有必要时需要使用 top 100 percent。示例如下:
exec SelectRange 'select top 100 percent * from 图书表名称 order by 图书编号','[id]','uniqueidentifier',5,3
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SelectRange]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SelectRange]
GO
CREATE PROCEDURE [dbo].[SelectRange]
@QueryString nvarchar(4000), --查询语句
@KeyFieldName nvarchar (250), --主键列、标识列或具有唯一值的列名称
@KeyFieldType nvarchar (250), --主键列、标识列或具有唯一值的列数据类型
@BeginIndex int, --指定起始索引,从0开始的整数,0表示第一条记录
@Count int=null --最多获取的记录数(行数)(如果取值为 NULL 则忽略此条件,获取从 @BeginIndex 起始的全部记录。)
as
set @KeyFieldName=N' '+@KeyFieldName
SET @KeyFieldType=N' '+@KeyFieldType
declare @sql nvarchar(4000)
declare @TempFieldName nvarchar(50)--临时字段名
set @TempFieldName=' [ID_'+cast(newid() as varchar(40))+']'
if @BeginIndex=0
begin
if @Count is not null
begin
set @sql=N'SELECT TOP '+cast(@BeginIndex+@Count as nvarchar(10))+N' * from ('+@QueryString+') a'
end
else
set @sql=@QueryString
end
else
begin
set @QueryString=N' ('+@QueryString+') a'
set @sql=N'declare @tb1 table(
'+@TempFieldName+N' int IDENTITY(1,1),
'+@KeyFieldName+@KeyFieldType+N'
)'
if @Count is not null
set @sql=@sql+N'
INSERT INTO @tb1 SELECT TOP '+cast(@BeginIndex+@Count as nvarchar(10))+@KeyFieldName+N' from'+@QueryString+N'
select * from'+@QueryString+N' where'+@KeyFieldName+N' in(
select '+@KeyFieldName+N' from @tb1 where' + @TempFieldName + N'>'+cast(@BeginIndex as nvarchar(10))+N' and '+@TempFieldName+N'<='+cast(@BeginIndex+@Count as nvarchar(10))+N'
)'
else
set @sql=@sql+N'
INSERT INTO @tb1 SELECT '+@KeyFieldName+N' from'+@QueryString+N'
select * from'+@QueryString+N' where'+@KeyFieldName+N' in(
select'+@KeyFieldName+N' from @tb1 where' + @TempFieldName + N'>'+cast(@BeginIndex as nvarchar(10))+N'
)'
end
EXEC ( @sql )
GO
临时表版本代码:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
/*--用存储过程实现的大数据集分页程序
显示指定查询结果的第X页(使用临时表)
最后修改日期:2008.09.06
Copy Right 2007.09 http://www.thinksea.com (传播时请保留此信息,谢谢!)
欢迎大家来信批评指正,作者的 Email:thinksea@163.com QQ:41464129
--调用示例
1、简单调用示例:
exec SelectPage 'select * from 图书表名称','[id]',5,3
2、对带排序的查询语句有必要时需要使用 top 100 percent。示例如下:
exec SelectPage 'select top 100 percent * from 图书表名称 order by 图书编号','[id]',5,3
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SelectPage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SelectPage]
GO
CREATE PROCEDURE [dbo].[SelectPage]
@QueryString nvarchar(4000), --查询语句
@KeyFieldName nvarchar (250), --主键列、标识列或具有唯一值的列名称
@PageIndex int, --指定分页索引,从0开始的整数,0表示第一页
@PageSize int --每页的大小(每页最多显示的记录数)
as
SET @KeyFieldName=N' '+@KeyFieldName
SET @QueryString=N' ('+@QueryString+N') a'
declare @sql nvarchar(4000)
declare @TempFieldName nvarchar(50)--临时字段名
set @TempFieldName=N' [ID_'+cast(newid() as varchar(40))+N']'
set @sql=N'SELECT '+@TempFieldName + N'=identity(int,1,1),'+@KeyFieldName+N' INTO #tb1 from'+@QueryString+N'
select * from'+@QueryString+N' where'+@KeyFieldName+N' in(
select '+@KeyFieldName+N' from #tb1 where' + @TempFieldName + N'>'+cast(@PageIndex*@PageSize as nvarchar(10))+N' and '+@TempFieldName+N'<='+cast(@PageIndex*@PageSize+@PageSize as nvarchar(10))+N'
)
declare @RecordsCount int,
@PagesCount int
select @RecordsCount=IDENT_CURRENT(''#tb1'')
set @PagesCount=ceiling((@RecordsCount+0.0)/'+cast(@PageSize as nvarchar(10))+N')
select @RecordsCount as [RecordsCount], @PagesCount as [PageCount]
drop table #tb1'
EXEC ( @sql )
GO
/*--用存储过程实现的大数据集分页程序
显示指定查询结果的从第X条记录开始的N条记录(使用临时表)
最后修改日期:2008.09.06
Copy Right 2007.09 http://www.thinksea.com (传播时请保留此信息,谢谢!)
欢迎大家来信批评指正,作者的 Email:thinksea@163.com QQ:41464129
--调用示例
1、简单调用示例:
exec SelectRange 'select * from 图书表名称','[id]',5,3
2、对带排序的查询语句有必要时需要使用 top 100 percent。示例如下:
exec SelectRange 'select top 100 percent * from 图书表名称 order by 图书编号','[id]',5,3
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SelectRange]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SelectRange]
GO
CREATE PROCEDURE [dbo].[SelectRange]
@QueryString nvarchar(4000), --查询语句
@KeyFieldName nvarchar (250), --主键列、标识列或具有唯一值的列名称
@BeginIndex int, --指定起始索引,从0开始的整数,0表示第一条记录
@Count int=null --最多获取的记录数(行数)(如果取值为 NULL 则忽略此条件,获取从 @BeginIndex 起始的全部记录。)
as
set @KeyFieldName=N' '+@KeyFieldName
declare @sql nvarchar(4000)
declare @TempFieldName nvarchar(50)--临时字段名
set @TempFieldName=' [ID_'+cast(newid() as varchar(40))+']'
if @BeginIndex=0
begin
if @Count is not null
begin
set @sql=N'SELECT TOP '+cast(@BeginIndex+@Count as nvarchar(10))+N' * from ('+@QueryString+') a'
end
else
set @sql=@QueryString
end
else
begin
set @QueryString=N' ('+@QueryString+') a'
if @Count is not null
set @sql=N'SELECT TOP '+cast(@BeginIndex+@Count as nvarchar(10))+@TempFieldName + N'=identity(int,1,1),'+@KeyFieldName+N' INTO #tb1 from'+@QueryString+N'
select * from'+@QueryString+N' where'+@KeyFieldName+N' in(
select '+@KeyFieldName+N' from #tb1 where' + @TempFieldName + N'>'+cast(@BeginIndex as nvarchar(10))+N' and '+@TempFieldName+N'<='+cast(@BeginIndex+@Count as nvarchar(10))+N'
)
drop table #tb1'
else
set @sql=N'SELECT '+@TempFieldName + N'=identity(int,1,1),'+@KeyFieldName+N' INTO #tb1 from'+@QueryString+N'
select * from'+@QueryString+N' where'+@KeyFieldName+N' in(
select'+@KeyFieldName+N' from #tb1 where' + @TempFieldName + N'>'+cast(@BeginIndex as nvarchar(10))+N'
)
drop table #tb1'
end
EXEC ( @sql )
GO
2008年9月16日:更新了“表变量版本代码”。在 SQL Server 2005 及 SQL Server 2008 中,“TOP 100 PERCENT ...... ORDER BY .....” 语法中的 ORDER BY 并不执行,这一点与 SQL 2000 不同,所以,为了同时兼容 SQL Server 2005 和 2008,增加了 @FieldOrder 参数用于单独明确指定排序条件。
注意:“临时表版本代码”也存在同样的问题,本次并为对其进行更新,如无特殊需要,以后也可能放弃“临时表版本代码”。
注意:“临时表版本代码”也存在同样的问题,本次并为对其进行更新,如无特殊需要,以后也可能放弃“临时表版本代码”。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
/*--用存储过程实现的大数据集分页程序
显示指定查询结果的第X页(使用表变量)
最后修改日期:2008.09.16
Copy Right 2007.09 http://www.thinksea.com (传播时请保留此信息,谢谢!)
欢迎大家来信批评指正,作者的 Email:thinksea@163.com QQ:41464129
--调用示例
1、简单调用示例:
exec SelectPage 'select * from 图书表名称','ORDER BY [id] DESC','[id]','uniqueidentifier',5,3
2、对带排序的查询语句有必要时需要使用 top 100 percent。示例如下:
exec SelectPage 'select top 100 percent * from 图书表名称 order by 图书编号','ORDER BY [id] DESC','[id]','uniqueidentifier',5,3
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SelectPage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SelectPage]
GO
CREATE PROCEDURE [dbo].[SelectPage]
@QueryString nvarchar(4000), --查询语句
@FieldOrder nvarchar (1000), /*排序字段列表
注意:因为数据物理存储顺序的不同,如果排序字段存在重复值可能会导致返回的指定分页记录集无效,此时需要附加一唯一值字段排序辅助完成排序功能。
例如:在一图书表中存在id(图书编号)和name(图书名称)两个字段,其中id字段是唯一值,name字段存在重复值,
如果从按照name进行排序的记录集中读取数据则应考虑使用“ORDER BY [name] asc, [id] asc”这样的排序字段列表,
其中“[id] asc”用来辅助“[name] asc”完成排序,对于按照name排序的结果中出现name值相同的多条记录按照id进行排序
*/
@KeyFieldName nvarchar (250), --主键列、标识列或具有唯一值的列名称
@KeyFieldType nvarchar (250), --主键列、标识列或具有唯一值的列数据类型
@PageIndex int, --指定分页索引,从0开始的整数,0表示第一页
@PageSize int --每页的大小(每页最多显示的记录数)
as
SET @KeyFieldName=N' '+@KeyFieldName
SET @KeyFieldType=N' '+@KeyFieldType
SET @QueryString=N' ('+@QueryString+N') a'
SET @FieldOrder=N' '+ISNULL(@FieldOrder, '')
declare @sql nvarchar(4000)
declare @TempFieldName nvarchar(50)--临时字段名
set @TempFieldName=N' [ID_'+cast(newid() as varchar(40))+N']'
set @sql=N'declare @tb1 table(
'+@TempFieldName+N' int IDENTITY(1,1),
'+@KeyFieldName+@KeyFieldType+N'
)
INSERT INTO @tb1 SELECT'+@KeyFieldName+N' from'+@QueryString+@FieldOrder+N'
select * from'+@QueryString+N' where'+@KeyFieldName+N' in(
select'+@KeyFieldName+N' from @tb1 where' + @TempFieldName + N'>'+cast(@PageIndex*@PageSize as nvarchar(10))+N' and '+@TempFieldName+N'<='+cast(@PageIndex*@PageSize+@PageSize as nvarchar(10))+N'
)'+@FieldOrder+N'
declare @RecordsCount int,
@PagesCount int
select @RecordsCount=count(*) from @tb1
set @PagesCount=ceiling((@RecordsCount+0.0)/'+cast(@PageSize as nvarchar(10))+N')
select @RecordsCount as [RecordsCount], @PagesCount as [PageCount]
'
EXEC ( @sql )
GO
/*--用存储过程实现的大数据集分页程序
显示指定查询结果的从第X条记录开始的N条记录(使用表变量)
最后修改日期:2008.09.16
Copy Right 2007.09 http://www.thinksea.com (传播时请保留此信息,谢谢!)
欢迎大家来信批评指正,作者的 Email:thinksea@163.com QQ:41464129
--调用示例
1、简单调用示例:
exec SelectRange 'select * from 图书表名称','ORDER BY [id] DESC','[id]',5,3,'图书编号,图书名称','图书编号'
2、对带排序的查询语句有必要时需要使用 top 100 percent。示例如下:
exec SelectRange 'select top 100 percent * from 图书表名称 order by 图书编号','ORDER BY [id] DESC','[id]','uniqueidentifier',5,3
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SelectRange]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SelectRange]
GO
CREATE PROCEDURE [dbo].[SelectRange]
@QueryString nvarchar(4000), --查询语句
@FieldOrder nvarchar (1000), /*排序字段列表
注意:因为数据物理存储顺序的不同,如果排序字段存在重复值可能会导致返回的指定分页记录集无效,此时需要附加一唯一值字段排序辅助完成排序功能。
例如:在一图书表中存在id(图书编号)和name(图书名称)两个字段,其中id字段是唯一值,name字段存在重复值,
如果从按照name进行排序的记录集中读取数据则应考虑使用“ORDER BY [name] asc, [id] asc”这样的排序字段列表,
其中“[id] asc”用来辅助“[name] asc”完成排序,对于按照name排序的结果中出现name值相同的多条记录按照id进行排序
*/
@KeyFieldName nvarchar (250), --主键列、标识列或具有唯一值的列名称
@KeyFieldType nvarchar (250), --主键列、标识列或具有唯一值的列数据类型
@BeginIndex int, --指定起始索引,从0开始的整数,0表示第一条记录
@Count int=null --最多获取的记录数(行数)(如果取值为 NULL 则忽略此条件,获取从 @BeginIndex 起始的全部记录。)
as
set @KeyFieldName=N' '+@KeyFieldName
SET @KeyFieldType=N' '+@KeyFieldType
SET @FieldOrder=N' '+ISNULL(@FieldOrder, '')
declare @sql nvarchar(4000)
declare @TempFieldName nvarchar(50)--临时字段名
set @TempFieldName=' [ID_'+cast(newid() as varchar(40))+']'
if @BeginIndex=0
begin
if @Count is not null
begin
set @sql=N'SELECT TOP '+cast(@Count as nvarchar(10))+N' * from ('+@QueryString+') a'+@FieldOrder
end
else
set @sql=@QueryString+@FieldOrder
end
else
begin
set @QueryString=N' ('+@QueryString+') a'
set @sql=N'declare @tb1 table(
'+@TempFieldName+N' int IDENTITY(1,1),
'+@KeyFieldName+@KeyFieldType+N'
)'
if @Count is not null
set @sql=@sql+N'
INSERT INTO @tb1 SELECT TOP '+cast(@BeginIndex+@Count as nvarchar(10))+@KeyFieldName+N' from'+@QueryString+@FieldOrder+N'
select * from'+@QueryString+N' where'+@KeyFieldName+N' in(
select '+@KeyFieldName+N' from @tb1 where' + @TempFieldName + N'>'+cast(@BeginIndex as nvarchar(10))+N' and '+@TempFieldName+N'<='+cast(@BeginIndex+@Count as nvarchar(10))+N'
)'+@FieldOrder
else
set @sql=@sql+N'
INSERT INTO @tb1 SELECT '+@KeyFieldName+N' from'+@QueryString+@FieldOrder+N'
select * from'+@QueryString+N' where'+@KeyFieldName+N' in(
select'+@KeyFieldName+N' from @tb1 where' + @TempFieldName + N'>'+cast(@BeginIndex as nvarchar(10))+N'
)'+@FieldOrder
end
EXEC ( @sql )
GO
2009年11月2日:
好久没有维护过这篇文章了,因为今天提交的代码版本修改于08年9月份,时间太久了,所以忘记了与之前版本相比具体做过什么调整,只记得在方便使用上做过调整,没有太多的时间去整理差异了,还是有劳感兴趣的朋友自己看吧。对了,还有后面那个应用示例仍然是前一版本的,麻烦大家自己改一下吧,反正也没几行代码是吧,呵呵 :-D
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
/*--用存储过程实现的大数据集分页程序
显示指定查询结果的第X页(使用表变量)
最后修改日期:2009.11.02
Copy Right 2007.09 http://www.thinksea.com (传播时请保留此信息,谢谢!)
欢迎大家来信批评指正,作者的 Email:thinksea@163.com QQ:41464129
--调用示例
1、简单调用示例:
exec SelectPage 'select * from 图书表名称','ORDER BY [id] DESC','[id]','uniqueidentifier',5,3
2、对带排序的查询语句有必要时需要使用 top 100 percent。示例如下:
exec SelectPage 'select top 100 percent * from 图书表名称 order by 图书编号','ORDER BY [id] DESC','[id]','uniqueidentifier',5,3
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SelectPage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SelectPage]
GO
CREATE PROCEDURE [dbo].[SelectPage]
@QueryString nvarchar(4000), --查询语句
@FieldOrder nvarchar (1000), /*排序字段列表,如果取值为 NULL 则忽略此条件。
注意:因为数据物理存储顺序的不同,如果排序字段存在重复值可能会导致返回的指定分页记录集无效,此时需要附加一唯一值字段排序辅助完成排序功能。
例如:在一图书表中存在id(图书编号)和name(图书名称)两个字段,其中id字段是唯一值,name字段存在重复值,
如果从按照name进行排序的记录集中读取数据则应考虑使用“ORDER BY [name] asc, [id] asc”这样的排序字段列表,
其中“[id] asc”用来辅助“[name] asc”完成排序,对于按照name排序的结果中出现name值相同的多条记录按照id进行排序
*/
@KeyFieldName nvarchar (250), --主键列、标识列或具有唯一值的列名称
@KeyFieldType nvarchar (250), --主键列、标识列或具有唯一值的列数据类型
@PageIndex int, --指定分页索引,从0开始的整数,0表示第一页
@PageSize int --每页的大小(每页最多显示的记录数)
as
SET @KeyFieldName=N' '+@KeyFieldName
SET @KeyFieldType=N' '+@KeyFieldType
SET @QueryString=N' ('+@QueryString+N') a'
SET @FieldOrder=N' '+ISNULL(@FieldOrder, '')
declare @sql nvarchar(4000)
declare @TempFieldName nvarchar(50)--临时字段名
set @TempFieldName=N' [ID_'+cast(newid() as varchar(40))+N']'
set @sql=N'declare @tb1 table(
'+@TempFieldName+N' int IDENTITY(1,1),
'+@KeyFieldName+@KeyFieldType+N'
)
INSERT INTO @tb1('+@KeyFieldName+') SELECT'+@KeyFieldName+N' from'+@QueryString+@FieldOrder+N'
select * from'+@QueryString+N' where'+@KeyFieldName+N' in(
select'+@KeyFieldName+N' from @tb1 where' + @TempFieldName + N'>'+cast(@PageIndex*@PageSize as nvarchar(10))+N' and '+@TempFieldName+N'<='+cast(@PageIndex*@PageSize+@PageSize as nvarchar(10))+N'
)'+@FieldOrder+N'
declare @RecordsCount int,
@PagesCount int
select @RecordsCount=count(*) from @tb1
set @PagesCount=ceiling((@RecordsCount+0.0)/'+cast(@PageSize as nvarchar(10))+N')
select @RecordsCount as [RecordsCount], @PagesCount as [PageCount]
'
EXEC ( @sql )
GO
/*--用存储过程实现的大数据集分页程序
显示指定查询结果的从第X条记录开始的N条记录(使用表变量)
最后修改日期:2009.11.02
Copy Right 2007.09 http://www.thinksea.com (传播时请保留此信息,谢谢!)
欢迎大家来信批评指正,作者的 Email:thinksea@163.com QQ:41464129
--调用示例
1、简单调用示例:
exec SelectRange 'select * from 图书表名称','ORDER BY [id] DESC','[id]',5,3,'图书编号,图书名称','图书编号'
2、对带排序的查询语句有必要时需要使用 top 100 percent。示例如下:
exec SelectRange 'select top 100 percent * from 图书表名称 order by 图书编号','ORDER BY [id] DESC','[id]','uniqueidentifier',5,3
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SelectRange]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SelectRange]
GO
CREATE PROCEDURE [dbo].[SelectRange]
@QueryString nvarchar(4000), --查询语句
@FieldOrder nvarchar (1000), /*排序字段列表,如果取值为 NULL 则忽略此条件。
注意:因为数据物理存储顺序的不同,如果排序字段存在重复值可能会导致返回的指定分页记录集无效,此时需要附加一唯一值字段排序辅助完成排序功能。
例如:在一图书表中存在id(图书编号)和name(图书名称)两个字段,其中id字段是唯一值,name字段存在重复值,
如果从按照name进行排序的记录集中读取数据则应考虑使用“ORDER BY [name] asc, [id] asc”这样的排序字段列表,
其中“[id] asc”用来辅助“[name] asc”完成排序,对于按照name排序的结果中出现name值相同的多条记录按照id进行排序
*/
@KeyFieldName nvarchar (250), --主键列、标识列或具有唯一值的列名称
@KeyFieldType nvarchar (250), --主键列、标识列或具有唯一值的列数据类型
@BeginIndex int, --指定起始索引,从0开始的整数,0表示第一条记录
@Count int=null --最多获取的记录数(行数)(如果取值为 NULL 则忽略此条件,获取从 @BeginIndex 起始的全部记录。)
as
set @KeyFieldName=N' '+@KeyFieldName
SET @KeyFieldType=N' '+@KeyFieldType
SET @FieldOrder=N' '+ISNULL(@FieldOrder, '')
declare @sql nvarchar(4000)
declare @TempFieldName nvarchar(50)--临时字段名
set @TempFieldName=' [ID_'+cast(newid() as varchar(40))+']'
if @BeginIndex=0
begin
if @Count is not null
begin
set @sql=N'SELECT TOP '+cast(@Count as nvarchar(10))+N' * from ('+@QueryString+') a'+@FieldOrder
end
else
set @sql=@QueryString+@FieldOrder
end
else
begin
set @QueryString=N' ('+@QueryString+') a'
set @sql=N'declare @tb1 table(
'+@TempFieldName+N' int IDENTITY(1,1),
'+@KeyFieldName+@KeyFieldType+N'
)'
if @Count is not null
set @sql=@sql+N'
INSERT INTO @tb1('+@KeyFieldName+') SELECT TOP '+cast(@BeginIndex+@Count as nvarchar(10))+@KeyFieldName+N' from'+@QueryString+@FieldOrder+N'
select * from'+@QueryString+N' where'+@KeyFieldName+N' in(
select '+@KeyFieldName+N' from @tb1 where' + @TempFieldName + N'>'+cast(@BeginIndex as nvarchar(10))+N' and '+@TempFieldName+N'<='+cast(@BeginIndex+@Count as nvarchar(10))+N'
)'+@FieldOrder
else
set @sql=@sql+N'
INSERT INTO @tb1('+@KeyFieldName+') SELECT '+@KeyFieldName+N' from'+@QueryString+@FieldOrder+N'
select * from'+@QueryString+N' where'+@KeyFieldName+N' in(
select'+@KeyFieldName+N' from @tb1 where' + @TempFieldName + N'>'+cast(@BeginIndex as nvarchar(10))+N'
)'+@FieldOrder
end
EXEC ( @sql )
GO
=============================================
表变量版本存储过程性能测试如下:
测试环境如下:P4 3.0,1G 内存,MS-SQL 2000
新闻类型数据,一百万记录,千条记录无重复,数据库文件大小4G
SQL 查询语句:带 Order By 排序和 Where 条件
页码(从0开始,0表示第一页) | 页尺寸 | 经历时间(多次测试的最短时间) | |
SelectPage | SelectRange | ||
0 | 100 | 3.36 秒 | 0.00 秒 |
5000 | 100 | 3.326 秒 | 0.033 秒 |
9999 | 100 | 3.233 秒 | 0.03 秒 |
注:因为对于不同的测试环境和查询语句都会有所差别,所以请以自己测试的实际性能为准。
===============================================
ASP 调用示例:
View Code
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
<%
dim SelectText, KeyField, KeyFieldType, PageIndex, PageSize
SelectText="select top 100 percent * from [News] order by [CreateTime]" '查询语句
KeyField="[ID]" '主键名称
KeyFieldType="uniqueidentifier" '主键类型
PageIndex=0 '从0开始的页索引
PageSize=10 '指示每页最多允许显示多少条记录
connString = "Driver={SQL Server};server=(local);uid=sa;pwd=sa;database=100w;"
Set conn = Server.Createobject("ADODB.Connection")
conn.open(connString)
set rs=server.createobject("adodb.recordset")
'------------------- 调用通用分页存储过程 ---------------
rs.open "SelectPage '"&SelectText&"','"&KeyField&"','"&KeyFieldType&"',"&PageIndex&","&PageSize&"'",conn,1,3
'------------------- 读取并显示记录 ---------------
dim index
index=1
do while not rs.EOF and not rs.BOF
Response.Write("<br />"&index&":"&rs("ID")&":"&rs("Subject"))
index = index + 1
rs.MoveNext()
loop
'------------------- 读取并显示分页信息 ---------------
Set rs = rs.NextRecordset()
if not rs.BOF then
Response.Write("<br />总记录数:"&rs("RecordsCount"))
Response.Write("<br />总页数:"&rs("PageCount"))
end if
rs.Close()
conn.Close()
%>