代码
/*
********************************************************************************
* Function: PROC_Pagination *
* Function: PROC_Pagination *
* 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
******************************************************************************** */
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