代码
/*
********************************************************************************* 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
此存储过程可产生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
形式二(适应大部分分页操作,可以任意字段排序,但是当数据量很大时,达到百万级时,效率会有所影响)
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
形式三(适合以唯一主键排序且数据量很大时)
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
