本文实例讲述了MSSQL分页存储过程。分享给大家供大家参考，具体如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
|
USE [DB_Common]
GO
/****** 对象: StoredProcedure [dbo].[Com_Pagination] 脚本日期: 03/09/2012 23:46:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/************************************************************
*
*Sql分页存储过程（支持多表分页存储）
*
*调用实例：
EXEC Com_Pagination 100, --总记录数
0, --总页数
-- 'Person',--查询的表名
'
Person p
LEFT JOIN TE a
ON a.PID=p.Id
', --查询的表名（这里为多表）
'a.*', --查询数据列
'p.ID', --排列字段
'p.ID', --分组字段
2, --每页记录数
1, --当前页数
0, --是否使用分组，否是
' a.pid=2'--查询条件
************************************************************/
CREATE PROCEDURE [dbo].[Com_Pagination]
@TotalCount INT OUTPUT, --总记录数
@TotalPage INT OUTPUT, --总页数
@Table NVARCHAR(1000), --查询的表名（可多表，例如：Person p LEFT JOIN TE a ON a.PID=p.Id ）
@Column NVARCHAR(1000), --查询的字段，可多列或者为*
@OrderColumn NVARCHAR(100), --排序字段
@GroupColumn NVARCHAR(150), --分组字段
@PageSize INT, --每页记录数
@CurrentPage INT, --当前页数
@Group TINYINT, --是否使用分组，否是
@Condition NVARCHAR(4000) --查询条件（注意：若这时候为多表查询，这里也可以跟条件，例如：a.pid=2）
AS
DECLARE @PageCount INT, --总页数
@strSql NVARCHAR(4000), --主查询语句
@strTemp NVARCHAR(2000), --临时变量
@strCount NVARCHAR(1000), --统计语句
@strOrderType NVARCHAR(1000) --排序语句
BEGIN
SET @PageCount = @PageSize * (@CurrentPage -1)
SET @strOrderType = ' ORDER BY ' + @OrderColumn + ' '
IF @Condition != ''
BEGIN
IF @CurrentPage = 1
BEGIN
IF @GROUP = 1
BEGIN
SET @strCount = 'SELECT @TotalCount=COUNT(*) FROM ' + @Table
+ ' WHERE ' + @Condition + ' GROUP BY ' + @GroupColumn
SET @strCount = @strCount + ' SET @TotalCount=@@ROWCOUNT'
SET @strSql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @Column
+ ' FROM ' + @Table + ' WHERE ' + @Condition +
' GROUP BY ' + @GroupColumn + ' ' + @strOrderType
END
ELSE
BEGIN
SET @strCount = 'SELECT @TotalCount=COUNT(*) FROM ' + @Table
+ ' WHERE ' + @Condition
SET @strSql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @Column
+ ' FROM ' + @Table + ' WHERE ' + @Condition + ' ' + @strOrderType
END
END
ELSE
BEGIN
IF @GROUP = 1
BEGIN
SET @strCount = 'SELECT @TotalCount=COUNT(*) FROM ' + @Table
+ ' WHERE ' + @Condition + ' GROUP BY ' + @GroupColumn
SET @strCount = @strCount + ' SET @TotalCount=@@ROWCOUNT'
SET @strSql = 'SELECT * FROM (SELECT TOP (2000) ' + @Column
+ ',ROW_NUMBER() OVER(' + @strOrderType +
') AS NUM FROM ' + @Table + ' WHERE ' + @Condition +
' GROUP BY ' + @GroupColumn +
') AS T WHERE NUM BETWEEN ' + STR(@PageCount + 1) +
' AND ' + STR(@PageCount + @PageSize)
END
ELSE
BEGIN
SET @strCount = 'SELECT @TotalCount=COUNT(*) FROM ' + @Table
+ ' WHERE ' + @Condition
SET @strSql = 'SELECT * FROM (SELECT TOP (2000) ' + @Column
+ ',ROW_NUMBER() OVER(' + @strOrderType +
') AS NUM FROM ' + @Table + ' WHERE ' + @Condition +
') AS T WHERE NUM BETWEEN ' + STR(@PageCount + 1) +
' AND ' + STR(@PageCount + @PageSize)
END
END
END
ELSE
--没有查询条件
BEGIN
IF @CurrentPage = 1
BEGIN
IF @GROUP = 1
BEGIN
SET @strCount = 'SELECT @TotalCount=COUNT(*) FROM ' + @Table
+ ' GROUP BY ' + @GroupColumn
SET @strCount = @strCount + 'SET @TotalCount=@@ROWCOUNT'
SET @strSql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @Column
+ ' FROM ' + @Table + ' GROUP BY ' + @GroupColumn + ' ' +
@strOrderType
END
ELSE
BEGIN
SET @strCount = 'SELECT @TotalCount=COUNT(*) FROM ' + @Table
SET @strSql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @Column
+ ' FROM ' + @Table + ' ' + @strOrderType
END
END
ELSE
BEGIN
IF @GROUP = 1
BEGIN
SET @strCount = 'SELECT @TotalCount=COUNT(*) FROM ' + @Table
+ ' GROUP BY ' + @GroupColumn
SET @strCount = @strCount + 'SET @TotalCount=@@ROWCOUNT'
SET @strSql = 'SELECT * FROM (SELECT TOP (2000) ' + @Column
+ ',ROW_NUMBER() OVER(' + @strOrderType +
') AS NUM FROM ' + @Table + ' GROUP BY ' + @GroupColumn +
') AS T WHERE NUM BETWEEN ' + STR(@PageCount + 1) +
' AND ' + STR(@PageCount + @PageSize)
END
ELSE
BEGIN
SET @strCount = 'SELECT @TotalCount=COUNT(*) FROM ' + @Table
SET @strSql = 'SELECT * FROM (SELECT TOP (2000) ' + @Column
+ ',ROW_NUMBER() OVER(' + @strOrderType +
') AS NUM FROM ' + @Table + ') AS T WHERE NUM BETWEEN ' +
STR(@PageCount + 1) + ' AND ' + STR(@PageCount + @PageSize)
END
END
END
EXEC sp_executesql @strCount,
N'@TotalCount INT OUTPUT',
@TotalCount OUTPUT
IF @TotalCount > 2000
BEGIN
SET @TotalCount = 2000
END
IF @TotalCount%@PageSize = 0
BEGIN
SET @TotalPage = @TotalCount / @PageSize
END
ELSE
BEGIN
SET @TotalPage = @TotalCount / @PageSize + 1
END
SET NOCOUNT ON
EXEC (@strSql)
END
SET NOCOUNT OFF
/**调用实例：
EXEC Com_Pagination 100, --总记录数
0, --总页数
-- 'Person',--查询的表名
'
Person p
LEFT JOIN TE a
ON a.PID=p.Id
', --查询的表名（这里为多表）
'a.*', --查询数据列
'p.ID', --排列字段
'p.ID', --分组字段
2, --每页记录数
1, --当前页数
0, --是否使用分组，否是
' a.pid=2'--查询条件
SELECT a.*
FROM Person p
LEFT JOIN TE a
ON a.PID = p.Id
WHERE a.pid = 2
**/
|
希望本文所述对大家SQL Server数据库程序设计有所帮助。