分页是我们开发过程中最常用到的功能,实现方式多种多样,根据项目开发中的体验,还是用存储过程分页比较不错,不管在性能上,还是方便性上都是个不错的选择。下面是用存储过程实现分页的一个思路,把源代码贴出来和大家分享。
转载或修改请注明出处,很辛苦弄出来的,哈哈^_^
USE
[
tempdb
]
GO
/**/
/* ***** Object: StoredProcedure [dbo].[PROC_COMM_PAGE_NAVIGATION] Script Date: 02/27/2007 16:32:01 ***** */
SET
ANSI_NULLS
ON
GO
SET
QUOTED_IDENTIFIER
ON
GO
/**/
/* ******************************************** function : page navigation create by :
create at : 2007-02-10 modify by : modify at : remark : case : copyright : http://blog.csdn.net/lxjhb *********************************************/
CREATE
PROCEDURE
[
dbo
]
.
[
PROC_COMM_PAGE_NAVIGATION
]
@TABLE_NAME
VARCHAR
(
8000
),
--
table name or view
@PK_NAME
VARCHAR
(
50
),
--
prime key column name
@SORT_COLUMN_NAME
VARCHAR
(
50
),
--
sort column
@SORT_TYPE
VARCHAR
(
10
),
--
sort type:asc,desc
@CURRENT_PAGE
INT
,
--
current page
@PAGE_SIZE
INT
--
page size
AS
SET
NOCOUNT
ON
BEGIN
DECLARE
@LONGSQL
VARCHAR
(
8000
) ,
@SHORTSQL
NVARCHAR
(
4000
) ,
@TAB
VARCHAR
(
40
) ,
@RECORD_COUNT
INT
SELECT
@TAB
=
'
PRT_
'
+
REPLACE
(
REPLACE
(
CONVERT
(
VARCHAR
,
GETDATE
(),
13
),
'
:
'
,
''
) ,
'
'
,
''
)
+
'
_
'
+
CONVERT
(
VARCHAR
,
@@SPID
)
SELECT
@LONGSQL
=
'
SELECT IDENTITY(INT,1,1) T1RNUM, T.*
'
+
'
INTO TEMPDB.DBO.
'
+
@TAB
+
'
FROM (
'
+
@TABLE_NAME
+
'
) T
'
+
'
ORDER BY T.
'
+
@SORT_COLUMN_NAME
+
'
'
+
CASE
WHEN
@SORT_TYPE
<>
'
DESC
'
THEN
'
ASC
'
ELSE
@SORT_TYPE
END
EXEC
(
@LONGSQL
)
SELECT
@RECORD_COUNT
=
@@ROWCOUNT
SELECT
@SHORTSQL
=
'
SELECT
'
+
MAX
(
CASE
WHEN
COLUMN_ID
=
2
THEN
'
'
+
C.NAME
ELSE
''
END
)
+
MAX
(
CASE
WHEN
COLUMN_ID
=
3
THEN
'
,
'
+
C.NAME
ELSE
''
END
)
+
MAX
(
CASE
WHEN
COLUMN_ID
=
4
THEN
'
,
'
+
C.NAME
ELSE
''
END
)
+
MAX
(
CASE
WHEN
COLUMN_ID
=
5
THEN
'
,
'
+
C.NAME
ELSE
''
END
)
+
MAX
(
CASE
WHEN
COLUMN_ID
=
6
THEN
'
,
'
+
C.NAME
ELSE
''
END
)
+
MAX
(
CASE
WHEN
COLUMN_ID
=
7
THEN
'
,
'
+
C.NAME
ELSE
''
END
)
+
MAX
(
CASE
WHEN
COLUMN_ID
=
8
THEN
'
,
'
+
C.NAME
ELSE
''
END
)
+
MAX
(
CASE
WHEN
COLUMN_ID
=
9
THEN
'
,
'
+
C.NAME
ELSE
''
END
)
+
MAX
(
CASE
WHEN
COLUMN_ID
=
10
THEN
'
,
'
+
C.NAME
ELSE
''
END
)
+
MAX
(
CASE
WHEN
COLUMN_ID
=
11
THEN
'
,
'
+
C.NAME
ELSE
''
END
)
+
MAX
(
CASE
WHEN
COLUMN_ID
=
12
THEN
'
,
'
+
C.NAME
ELSE
''
END
)
+
MAX
(
CASE
WHEN
COLUMN_ID
=
13
THEN
'
,
'
+
C.NAME
ELSE
''
END
)
+
MAX
(
CASE
WHEN
COLUMN_ID
=
14
THEN
'
,
'
+
C.NAME
ELSE
''
END
)
+
MAX
(
CASE
WHEN
COLUMN_ID
=
15
THEN
'
,
'
+
C.NAME
ELSE
''
END
)
+
MAX
(
CASE
WHEN
COLUMN_ID
=
16
THEN
'
,
'
+
C.NAME
ELSE
''
END
)
+
MAX
(
CASE
WHEN
COLUMN_ID
=
17
THEN
'
,
'
+
C.NAME
ELSE
''
END
)
+
MAX
(
CASE
WHEN
COLUMN_ID
=
18
THEN
'
,
'
+
C.NAME
ELSE
''
END
)
+
MAX
(
CASE
WHEN
COLUMN_ID
=
19
THEN
'
,
'
+
C.NAME
ELSE
''
END
)
+
MAX
(
CASE
WHEN
COLUMN_ID
=
20
THEN
'
,
'
+
C.NAME
ELSE
''
END
)
+
MAX
(
CASE
WHEN
COLUMN_ID
=
21
THEN
'
,
'
+
C.NAME
ELSE
''
END
)
+
MAX
(
CASE
WHEN
COLUMN_ID
=
22
THEN
'
,
'
+
C.NAME
ELSE
''
END
)
+
MAX
(
CASE
WHEN
COLUMN_ID
=
23
THEN
'
,
'
+
C.NAME
ELSE
''
END
)
+
MAX
(
CASE
WHEN
COLUMN_ID
=
24
THEN
'
,
'
+
C.NAME
ELSE
''
END
)
+
MAX
(
CASE
WHEN
COLUMN_ID
=
25
THEN
'
,
'
+
C.NAME
ELSE
''
END
)
+
MAX
(
CASE
WHEN
COLUMN_ID
=
26
THEN
'
,
'
+
C.NAME
ELSE
''
END
)
+
MAX
(
CASE
WHEN
COLUMN_ID
=
27
THEN
'
,
'
+
C.NAME
ELSE
''
END
)
+
MAX
(
CASE
WHEN
COLUMN_ID
=
28
THEN
'
,
'
+
C.NAME
ELSE
''
END
)
+
MAX
(
CASE
WHEN
COLUMN_ID
=
29
THEN
'
,
'
+
C.NAME
ELSE
''
END
)
+
MAX
(
CASE
WHEN
COLUMN_ID
=
30
THEN
'
,
'
+
C.NAME
ELSE
''
END
)
+
'
FROM TEMPDB.DBO.
'
+
@TAB
+
'
WHERE T1RNUM >
'
+
CONVERT
(
VARCHAR
, (
CASE
WHEN
@CURRENT_PAGE
<=
0
THEN
1
WHEN
@CURRENT_PAGE
>=
CEILING
(
CONVERT
(
DECIMAL
(
15
,
2
),
@RECORD_COUNT
)
/
@PAGE_SIZE
)
THEN
CEILING
(
CONVERT
(
DECIMAL
(
15
,
2
),
@RECORD_COUNT
)
/
@PAGE_SIZE
)
ELSE
@CURRENT_PAGE
END
-
1
)
*
@PAGE_SIZE
)
+
'
AND T1RNUM <=
'
+
CONVERT
(
VARCHAR
,
CASE
WHEN
@CURRENT_PAGE
<=
0
THEN
1
WHEN
@CURRENT_PAGE
>=
CEILING
(
CONVERT
(
DECIMAL
(
15
,
2
),
@RECORD_COUNT
)
/
@PAGE_SIZE
)
THEN
CEILING
(
CONVERT
(
DECIMAL
(
15
,
2
),
@RECORD_COUNT
)
/
@PAGE_SIZE
)
ELSE
@CURRENT_PAGE
END
*
@PAGE_SIZE
)
FROM
TEMPDB.SYS.OBJECTS O , TEMPDB.SYS.COLUMNS C
WHERE
O.TYPE
=
'
U
'
AND
O.
OBJECT_ID
=
C.
OBJECT_ID
AND
O.NAME
=
@TAB
--
return result
SELECT
@RECORD_COUNT
AS
'
RECORD_COUNT
'
,
CEILING
(
CONVERT
(
DECIMAL
(
15
,
2
),
@RECORD_COUNT
)
/
@PAGE_SIZE
)
AS
'
PAGE_COUNT
'
,
CASE
WHEN
@CURRENT_PAGE
<=
0
THEN
1
WHEN
@CURRENT_PAGE
>=
CEILING
(
CONVERT
(
DECIMAL
(
15
,
2
),
@RECORD_COUNT
)
/
@PAGE_SIZE
)
THEN
CEILING
(
CONVERT
(
DECIMAL
(
15
,
2
),
@RECORD_COUNT
)
/
@PAGE_SIZE
)
ELSE
@CURRENT_PAGE
END
AS
'
CURRENT_PAGE
'
,
@PAGE_SIZE
AS
'
PAGE_SIZE
'
,
@CURRENT_PAGE
AS
'
PAGE_IN
'
EXEC
DBO.SP_EXECUTESQL
@SHORTSQL
--
drop temp table
SELECT
@SHORTSQL
=
'
DROP TABLE TEMPDB.DBO.
'
+
@TAB
EXEC
DBO.SP_EXECUTESQL
@SHORTSQL
END
SET
NOCOUNT
OFF