高效分页存储过程[转]
代码/*********************************************************************************
* Copyright (C) 2010 www.cnblogs.com/immensity,All Rights Reserved *
* Function: PROC_Pagination *
* Description: *
* 高效率通用分页存储过程 *
* Author: *
* 张盼(ZP) *
* immensitybiz@163.com *
* http://www.cnblogs.com/immensity *
* Finish DateTime: *
* 2010/04/08 *
* History: *
* 2008/02/07 *
* Example: *
* EXEC PROC_Pagination @TableName='表名',@Orderfld='排序列名' *
*********************************************************************************/
CREATE PROCEDURE [dbo].[PROC_Pagination]
(
@TableName VARCHAR(50), --表名
@Where VARCHAR(1000) = '', --查询条件
@ReturnFields VARCHAR(500) = '*', --返回的列
@Orderfld VARCHAR(100), --排序的列(多页以,分隔)
@OrderType BIT = true, --排序模式(默认为true,表示降序)
@FKColumn VARCHAR(50) = '', --主键列
@PageIndex INT = 1, --行索引
@PageSize INT = 10 --页大小
)
AS
BEGIN
--当前页索引处理(争对第二和第三种情况,前台使用时@PageIndex起始值为1) 开始
IF(@PageIndex<1)
SET @PageIndex=1
ELSE
SET @PageIndex=@PageIndex-1
--当前页索引处理(争对第二和第三种情况,前台使用时@PageIndex起始值为1) 结束
DECLARE @SQL NVARCHAR(2000)--动态SQL语句
DECLARE @OrderColumnCrux VARCHAR(50)--排序核心 值如下:>(SELECT MAX( 或 <(SELECT MIN(
SET @OrderColumnCrux='<(SELECT MIN('--默认为降序模式
SET @ReturnFields=' '+@ReturnFields+' '--避免错误 在查询的列的前后加一个空格
DECLARE @OrderBy NVARCHAR(255) --排序
DECLARE @RowCount NVARCHAR(1000)--拼接查询行数的SQL语句
--处理排序开始
IF(@Orderfld IS NOT NULL AND @Orderfld<>'')
BEGIN
--降序
IF(@OrderType=1)
BEGIN
SET @OrderBy=' ORDER BY ' + REPLACE(@Orderfld,',',' DESC,') + ' DESC '
SET @OrderColumnCrux='<(SELECT MIN('
END
ELSE--否则为降序
BEGIN
SET @OrderBy = ' ORDER BY ' + REPLACE(@Orderfld,',',' ASC,') + ' ASC '
SET @OrderColumnCrux='>(SELECT MAX('
END
END
--当无主键时候(情况一)
IF(@FKColumn IS NULL OR @FKColumn = '')
BEGIN
--当前页索引处理(争对当前情况,前台使用时@PageIndex起始值为1) 开始
IF(@PageIndex<=0)
SET @PageIndex=1
ELSE
SET @PageIndex=@PageIndex+1
--当前页索引处理(争对当前情况,前台使用时@PageIndex起始值为1) 结束
DECLARE @TotalRecord int --记录总数
DECLARE @TotalPage int --页总数
DECLARE @CurrentPageSize int --当前页面数据数量
DECLARE @TotalRecordForPageIndex int
DECLARE @CutOrderBy nvarchar(255)
DECLARE @CurrentWhere nvarchar(1000)--当前情况下的where条件
--降序
IF @OrderType = 1
BEGIN
SET @CutOrderBy = ' Order by '+ REPLACE(@Orderfld,',',' asc,') + ' asc '
END
ELSE
BEGIN
SET @CutOrderBy = ' Order by '+ REPLACE(@Orderfld,',',' desc,') + ' desc '
END
--计算
SET @TotalPage=(@TotalRecord-1)/@PageSize+1
SET @CurrentPageSize=@PageSize
IF(@TotalPage=@PageIndex)
BEGIN
SET @CurrentPageSize=@TotalRecord%@PageSize
IF(@CurrentPageSize=0)
SET @CurrentPageSize=@PageSize
END
-- 返回记录
set @TotalRecordForPageIndex=@PageIndex*@PageSize
--查询条件
SET @CurrentWhere=''
IF(@Where IS NOT NULL AND @Where <>'')
BEGIN
SET @CurrentWhere=' WHERE 1=1 AND '+@Where
END
SET @SQL='SELECT * FROM
(SELECT TOP '+STR(@CurrentPageSize)+' * FROM
(SELECT TOP '+STR(@TotalRecordForPageIndex)+' '+@ReturnFields+'
FROM '+@TableName+' '+@CurrentWhere+' '+@OrderBy+') TB2
'+@CutOrderBy+') TB3
'+@OrderBy
--执行查询
EXEC SP_EXECUTESQL @SQL
--为查询表行数准备数据
IF(@Where IS NOT NULL AND @Where <>'')
BEGIN
SET @RowCount='SELECT COUNT(*) FROM '+@TableName+' WHERE 1=1 AND '+@Where
END
ELSE
BEGIN
SET @RowCount='SELECT COUNT(*) FROM '+@TableName
END
EXEC SP_EXECUTESQL @RowCount--执行查询 得到表行总数
END
--主键与排序字段相同(情况二)
ELSE IF(@Orderfld=@FKColumn)
BEGIN
IF(@PageIndex IS NOT NULL AND @PageIndex<>0)
BEGIN
--当查询条件不为空
IF(@Where IS NOT NULL AND @Where <>'')
BEGIN
--处理SQL语句
SET @SQL='SELECT TOP '+Convert(varchar(5),@PageSize)+' '+@ReturnFields+' FROM '+@TableName
+' WHERE('+@FKColumn+@OrderColumnCrux+@FKColumn+') FROM (SELECT TOP '+Convert(varchar(5),(@PageIndex*@PageSize))
+' '+@FKColumn+' FROM '+@TableName+' WHERE 1=1 AND '+@Where+' '+@OrderBy+') AS T)) AND '+@Where+' '+@OrderBy
END
ELSE
BEGIN
--处理SQL语句
SET @SQL='SELECT TOP '+Convert(varchar(5),@PageSize)+' '+@ReturnFields+' FROM '+@TableName
+' WHERE('+@FKColumn+@OrderColumnCrux+@FKColumn+') FROM (SELECT TOP '+Convert(varchar(5),(@PageIndex*@PageSize))
+' '+@FKColumn+' FROM '+@TableName+' '+@OrderBy+') AS T)) '+@OrderBy
END
END
ELSE
BEGIN
IF(@Where IS NOT NULL AND @Where <>'')--当查询条件不为空
BEGIN
--处理SQL语句
SET @SQL='SELECT TOP '+Convert(varchar(5),@PageSize)+' '+@ReturnFields+' FROM '+@TableName
+' WHERE 1=1 AND '+@Where+' '+@OrderBy
END
ELSE
BEGIN
--处理SQL语句
SET @SQL='SELECT TOP '+Convert(varchar(5),@PageSize)+' '+@ReturnFields+' FROM '+@TableName
+' '+@OrderBy
END
END
EXEC SP_EXECUTESQL @SQL--执行查询
--为查询表行数准备数据
IF(@Where IS NOT NULL AND @Where <>'')
BEGIN
SET @RowCount='SELECT COUNT(*) FROM '+@TableName+' WHERE 1=1 AND '+@Where
END
ELSE
BEGIN
SET @RowCount='SELECT COUNT(*) FROM '+@TableName
END
EXEC SP_EXECUTESQL @RowCount--执行查询 得到表行总数
END
--主键与排序字段不同(情况三)
ELSE
BEGIN
IF(@Where IS NOT NULL AND @Where <>'')--当查询条件不为空
BEGIN
SET @SQL='SELECT TOP '+Convert(varchar(5),@PageSize)+' '+@ReturnFields+' FROM '+@TableName
+' WHERE '+@FKColumn+' NOT IN'
+' (SELECT TOP '+Convert(varchar(5),(@PageIndex*@PageSize))+' '+@FKColumn+' FROM '+@TableName
+' WHERE 1=1 AND '+@Where+' '+@OrderBy+') AND '+@Where+' '+@OrderBy
EXEC SP_EXECUTESQL @SQL--执行查询
SET @RowCount='SELECT COUNT(*) FROM '+@TableName+' WHERE 1=1 AND '+@Where
EXEC SP_EXECUTESQL @RowCount--执行查询 得到表行总数
END
ELSE
BEGIN
SET @SQL='SELECT TOP '+Convert(varchar(5),@PageSize)+' '+@ReturnFields+' FROM '+@TableName
+' WHERE '+@FKColumn+' NOT IN'
+' (SELECT TOP '+Convert(varchar(5),(@PageIndex*@PageSize))+' '+@FKColumn+' FROM '+@TableName
+' '+@OrderBy+') '+@OrderBy
EXEC SP_EXECUTESQL @SQL--执行查询
SET @RowCount='SELECT COUNT(*) FROM '+@TableName
EXEC SP_EXECUTESQL @RowCount--执行查询 得到表行总数
END
END
END
* Copyright (C) 2010 www.cnblogs.com/immensity,All Rights Reserved *
* Function: PROC_Pagination *
* Description: *
* 高效率通用分页存储过程 *
* Author: *
* 张盼(ZP) *
* immensitybiz@163.com *
* http://www.cnblogs.com/immensity *
* Finish DateTime: *
* 2010/04/08 *
* History: *
* 2008/02/07 *
* Example: *
* EXEC PROC_Pagination @TableName='表名',@Orderfld='排序列名' *
*********************************************************************************/
CREATE PROCEDURE [dbo].[PROC_Pagination]
(
@TableName VARCHAR(50), --表名
@Where VARCHAR(1000) = '', --查询条件
@ReturnFields VARCHAR(500) = '*', --返回的列
@Orderfld VARCHAR(100), --排序的列(多页以,分隔)
@OrderType BIT = true, --排序模式(默认为true,表示降序)
@FKColumn VARCHAR(50) = '', --主键列
@PageIndex INT = 1, --行索引
@PageSize INT = 10 --页大小
)
AS
BEGIN
--当前页索引处理(争对第二和第三种情况,前台使用时@PageIndex起始值为1) 开始
IF(@PageIndex<1)
SET @PageIndex=1
ELSE
SET @PageIndex=@PageIndex-1
--当前页索引处理(争对第二和第三种情况,前台使用时@PageIndex起始值为1) 结束
DECLARE @SQL NVARCHAR(2000)--动态SQL语句
DECLARE @OrderColumnCrux VARCHAR(50)--排序核心 值如下:>(SELECT MAX( 或 <(SELECT MIN(
SET @OrderColumnCrux='<(SELECT MIN('--默认为降序模式
SET @ReturnFields=' '+@ReturnFields+' '--避免错误 在查询的列的前后加一个空格
DECLARE @OrderBy NVARCHAR(255) --排序
DECLARE @RowCount NVARCHAR(1000)--拼接查询行数的SQL语句
--处理排序开始
IF(@Orderfld IS NOT NULL AND @Orderfld<>'')
BEGIN
--降序
IF(@OrderType=1)
BEGIN
SET @OrderBy=' ORDER BY ' + REPLACE(@Orderfld,',',' DESC,') + ' DESC '
SET @OrderColumnCrux='<(SELECT MIN('
END
ELSE--否则为降序
BEGIN
SET @OrderBy = ' ORDER BY ' + REPLACE(@Orderfld,',',' ASC,') + ' ASC '
SET @OrderColumnCrux='>(SELECT MAX('
END
END
--当无主键时候(情况一)
IF(@FKColumn IS NULL OR @FKColumn = '')
BEGIN
--当前页索引处理(争对当前情况,前台使用时@PageIndex起始值为1) 开始
IF(@PageIndex<=0)
SET @PageIndex=1
ELSE
SET @PageIndex=@PageIndex+1
--当前页索引处理(争对当前情况,前台使用时@PageIndex起始值为1) 结束
DECLARE @TotalRecord int --记录总数
DECLARE @TotalPage int --页总数
DECLARE @CurrentPageSize int --当前页面数据数量
DECLARE @TotalRecordForPageIndex int
DECLARE @CutOrderBy nvarchar(255)
DECLARE @CurrentWhere nvarchar(1000)--当前情况下的where条件
--降序
IF @OrderType = 1
BEGIN
SET @CutOrderBy = ' Order by '+ REPLACE(@Orderfld,',',' asc,') + ' asc '
END
ELSE
BEGIN
SET @CutOrderBy = ' Order by '+ REPLACE(@Orderfld,',',' desc,') + ' desc '
END
--计算
SET @TotalPage=(@TotalRecord-1)/@PageSize+1
SET @CurrentPageSize=@PageSize
IF(@TotalPage=@PageIndex)
BEGIN
SET @CurrentPageSize=@TotalRecord%@PageSize
IF(@CurrentPageSize=0)
SET @CurrentPageSize=@PageSize
END
-- 返回记录
set @TotalRecordForPageIndex=@PageIndex*@PageSize
--查询条件
SET @CurrentWhere=''
IF(@Where IS NOT NULL AND @Where <>'')
BEGIN
SET @CurrentWhere=' WHERE 1=1 AND '+@Where
END
SET @SQL='SELECT * FROM
(SELECT TOP '+STR(@CurrentPageSize)+' * FROM
(SELECT TOP '+STR(@TotalRecordForPageIndex)+' '+@ReturnFields+'
FROM '+@TableName+' '+@CurrentWhere+' '+@OrderBy+') TB2
'+@CutOrderBy+') TB3
'+@OrderBy
--执行查询
EXEC SP_EXECUTESQL @SQL
--为查询表行数准备数据
IF(@Where IS NOT NULL AND @Where <>'')
BEGIN
SET @RowCount='SELECT COUNT(*) FROM '+@TableName+' WHERE 1=1 AND '+@Where
END
ELSE
BEGIN
SET @RowCount='SELECT COUNT(*) FROM '+@TableName
END
EXEC SP_EXECUTESQL @RowCount--执行查询 得到表行总数
END
--主键与排序字段相同(情况二)
ELSE IF(@Orderfld=@FKColumn)
BEGIN
IF(@PageIndex IS NOT NULL AND @PageIndex<>0)
BEGIN
--当查询条件不为空
IF(@Where IS NOT NULL AND @Where <>'')
BEGIN
--处理SQL语句
SET @SQL='SELECT TOP '+Convert(varchar(5),@PageSize)+' '+@ReturnFields+' FROM '+@TableName
+' WHERE('+@FKColumn+@OrderColumnCrux+@FKColumn+') FROM (SELECT TOP '+Convert(varchar(5),(@PageIndex*@PageSize))
+' '+@FKColumn+' FROM '+@TableName+' WHERE 1=1 AND '+@Where+' '+@OrderBy+') AS T)) AND '+@Where+' '+@OrderBy
END
ELSE
BEGIN
--处理SQL语句
SET @SQL='SELECT TOP '+Convert(varchar(5),@PageSize)+' '+@ReturnFields+' FROM '+@TableName
+' WHERE('+@FKColumn+@OrderColumnCrux+@FKColumn+') FROM (SELECT TOP '+Convert(varchar(5),(@PageIndex*@PageSize))
+' '+@FKColumn+' FROM '+@TableName+' '+@OrderBy+') AS T)) '+@OrderBy
END
END
ELSE
BEGIN
IF(@Where IS NOT NULL AND @Where <>'')--当查询条件不为空
BEGIN
--处理SQL语句
SET @SQL='SELECT TOP '+Convert(varchar(5),@PageSize)+' '+@ReturnFields+' FROM '+@TableName
+' WHERE 1=1 AND '+@Where+' '+@OrderBy
END
ELSE
BEGIN
--处理SQL语句
SET @SQL='SELECT TOP '+Convert(varchar(5),@PageSize)+' '+@ReturnFields+' FROM '+@TableName
+' '+@OrderBy
END
END
EXEC SP_EXECUTESQL @SQL--执行查询
--为查询表行数准备数据
IF(@Where IS NOT NULL AND @Where <>'')
BEGIN
SET @RowCount='SELECT COUNT(*) FROM '+@TableName+' WHERE 1=1 AND '+@Where
END
ELSE
BEGIN
SET @RowCount='SELECT COUNT(*) FROM '+@TableName
END
EXEC SP_EXECUTESQL @RowCount--执行查询 得到表行总数
END
--主键与排序字段不同(情况三)
ELSE
BEGIN
IF(@Where IS NOT NULL AND @Where <>'')--当查询条件不为空
BEGIN
SET @SQL='SELECT TOP '+Convert(varchar(5),@PageSize)+' '+@ReturnFields+' FROM '+@TableName
+' WHERE '+@FKColumn+' NOT IN'
+' (SELECT TOP '+Convert(varchar(5),(@PageIndex*@PageSize))+' '+@FKColumn+' FROM '+@TableName
+' WHERE 1=1 AND '+@Where+' '+@OrderBy+') AND '+@Where+' '+@OrderBy
EXEC SP_EXECUTESQL @SQL--执行查询
SET @RowCount='SELECT COUNT(*) FROM '+@TableName+' WHERE 1=1 AND '+@Where
EXEC SP_EXECUTESQL @RowCount--执行查询 得到表行总数
END
ELSE
BEGIN
SET @SQL='SELECT TOP '+Convert(varchar(5),@PageSize)+' '+@ReturnFields+' FROM '+@TableName
+' WHERE '+@FKColumn+' NOT IN'
+' (SELECT TOP '+Convert(varchar(5),(@PageIndex*@PageSize))+' '+@FKColumn+' FROM '+@TableName
+' '+@OrderBy+') '+@OrderBy
EXEC SP_EXECUTESQL @SQL--执行查询
SET @RowCount='SELECT COUNT(*) FROM '+@TableName
EXEC SP_EXECUTESQL @RowCount--执行查询 得到表行总数
END
END
END
此存储过程可产生3形式中SQL语句
形式一(主要实现对无主键表的分页,可以任意字段排序,但是效率很低)
exec [PROC_Pagination] 'articles',@Orderfld='articleid',@PageIndex=2
SELECT * FROM (SELECT TOP 10 * FROM
(SELECT TOP 20 * FROM articles ORDER BY articleid ASC ) TB2
Order by articleid desc ) TB3
ORDER BY articleid ASC
(SELECT TOP 20 * FROM articles ORDER BY articleid ASC ) TB2
Order by articleid desc ) TB3
ORDER BY articleid ASC
形式二(适应大部分分页操作,可以任意字段排序,但是当数据量很大时,达到百万级时,效率会有所影响)
exec [PROC_Pagination] 'articles',@Orderfld='Title',@PageIndex=2,@FKColumn='articleid'
SELECT TOP 10 * FROM articles WHERE articleid NOT IN
(SELECT TOP 10 articleid FROM articles ORDER BY articleid ASC )
ORDER BY articleid ASC
(SELECT TOP 10 articleid FROM articles ORDER BY articleid ASC )
ORDER BY articleid ASC
形式三(适合以唯一主键排序且数据量很大时)
exec [PROC_Pagination] 'articles',@Orderfld='articleid',@PageIndex=2,@FKColumn='articleid'
SELECT TOP 10 * FROM articles WHERE(articleid>(SELECT MAX(articleid)
FROM (SELECT TOP 10 articleid FROM articles ORDER BY articleid ASC ) AS T))
ORDER BY articleid ASC
FROM (SELECT TOP 10 articleid FROM articles ORDER BY articleid ASC ) AS T))
ORDER BY articleid ASC
转自：http://www.cnblogs.com/immensity
