1
Community_Common_Count_USP
2
/*
3
作者: SLIGHTBOY
4
建立日期: 2005-8-17
5
修改日期:
6
存储作用: 通用记录统计
7
作用数据库: 自定义
8
输入变量:
9
@KeyColumn 主键字段名
10
@TableName 表格名称
11
@SearchQuery 查询条件 默认 ''
12
输出变量:
13
中间变量:
14
*/
15
CREATE PROCEDURE dbo.Community_Common_Count_USP
16
(
17
@KeyColumn varchar(20),
18
@TableName varchar(20),
19
@SearchQuery varchar(400) = ''
20
)
21
AS
22
SET NOCOUNT ON
23
DECLARE @Timer datetime
24
SET @Timer = getdate()
25![](/Images/OutliningIndicators/None.gif)
26
IF ( @SearchQuery IS Not NULL ) AND ( @SearchQuery <> '' )
27
exec('SELECT Count('+ @KeyColumn +') FROM ['+ @TableName +'] Where '+ @SearchQuery)
28
Else
29
exec('SELECT Count('+ @KeyColumn +') FROM ['+ @TableName +']')
30![](/Images/OutliningIndicators/None.gif)
31
print(DATEDIFF(millisecond, @Timer, getdate()))
32
GO
33![](/Images/OutliningIndicators/None.gif)
34![](/Images/OutliningIndicators/None.gif)
35
Community_Common_Page_USP
36
/*
37
作者: SLIGHTBOY
38
建立日期: 2005-8-12
39
修改日期:
40
存储作用: 通用分页存储过程
41
作用数据库: 自定义
42
版本: 1.0
43
输入变量:
44
@AbsolutePage 请求页 默认 1
45
@PageSize 每页记录数 默认 20
46
@RecordCount 总记录
47
@KeyColumn 主键字段名
48
@SelectColumn 查询字段
49
@TableName 表格名称
50
@Sort 排序方式 DESC
51
@SearchQuery 查询条件 默认 ''
52
@ExpandQuery 扩展内容(FOR XML EXPLICIT) 默认 ''
53
输出变量:
54
过程变量
55
@SqlQuery 查询语句
56
@AbsolutePosition 当前位置
57
*/
58
CREATE PROCEDURE dbo.Community_Common_Page_USP
59
(
60
@AbsolutePage int = 1,
61
@PageSize int = 20,
62
@RecordCount int,
63
@KeyColumn varchar(20),
64
@SelectColumn varchar(1000),
65
@TableName varchar(20),
66
@Sort varchar(4) = '',
67
@SearchQuery varchar(400) = '',
68
@ExpandQuery varchar(50) = ''
69
)
70
AS
71
SET NOCOUNT ON
72
DECLARE @Timer datetime
73
SET @Timer = getdate()
74![](/Images/OutliningIndicators/None.gif)
75
DECLARE @SqlQuery varchar(3000)
76
DECLARE @AbsolutePosition int
77
SET @AbsolutePosition = @AbsolutePage * @PageSize
78![](/Images/OutliningIndicators/None.gif)
79
IF ( @AbsolutePosition = @PageSize )
80
IF ( @Sort = 1 )
81
SET @SqlQuery = '
82
SELECT TOP '+ LTRIM( STR( @PageSize ) ) +'
83
'+ @SelectColumn +'
84
FROM ['+ @TableName +']
85
'+ dbo.iif(@SearchQuery, 'Where '+ @SearchQuery, '' ) +'
86
ORDER BY '+ @KeyColumn +' ASC
87
'+ @ExpandQuery
88
Else
89
90
SET @SqlQuery = '
91
SELECT TOP '+ LTRIM( STR( @PageSize ) ) +'
92
'+ @SelectColumn +'
93
FROM ['+ @TableName +']
94
'+ dbo.iif(@SearchQuery, 'Where '+ @SearchQuery, '' ) +'
95
ORDER BY '+ @KeyColumn +' DESC
96
'+ @ExpandQuery
97
-- 大于 首页 小于 中间页
98
Else IF ( @AbsolutePosition <= @RecordCount/2 )
99
IF ( @Sort = 1 )
100
SET @SqlQuery = '
101
SELECT TOP '+ LTRIM( STR( @PageSize ) ) +' '+ @SelectColumn +'
102
FROM ['+ @TableName +']
103
WHERE '+ @KeyColumn +' >
104
(
105
SELECT MAX('+ @KeyColumn +')
106
FROM
107
(
108
SELECT TOP '+ LTRIM( STR( ( @AbsolutePage - 1)*@PageSize) ) +'
109
'+ @KeyColumn +'
110
FROM ['+ @TableName +']
111
'+ dbo.iif(@SearchQuery, 'Where '+ @SearchQuery, '' ) +'
112
ORDER BY '+ @KeyColumn +' ASC
113
) As Child
114
)
115
'+ dbo.iif(@SearchQuery, ' AND '+ @SearchQuery, '' ) +'
116
ORDER BY '+ @KeyColumn +' ASC
117
'+ @ExpandQuery
118
Else
119
SET @SqlQuery = '
120
SELECT TOP '+ LTRIM( STR( @PageSize ) ) +' '+ @SelectColumn +'
121
FROM ['+ @TableName +']
122
WHERE '+ @KeyColumn +' <=
123
(
124
SELECT Min('+ @KeyColumn +')
125
FROM
126
(
127
SELECT TOP '+ LTRIM( STR( ( @AbsolutePage - 1)*@PageSize + 1 ) ) +'
128
'+ @KeyColumn +'
129
FROM ['+ @TableName +']
130
'+ dbo.iif(@SearchQuery, 'Where '+ @SearchQuery, '' ) +'
131
ORDER BY '+ @KeyColumn +' DESC
132
) As Child
133
)
134
'+ dbo.iif(@SearchQuery, ' AND '+ @SearchQuery, '' ) +'
135
ORDER BY '+ @KeyColumn +' DESC
136
'+ @ExpandQuery
137
Else IF (@AbsolutePosition > @RecordCount/2 )
138
IF ( @Sort = 1 )
139
SET @SqlQuery = '
140
SELECT TOP '+ LTRIM( STR( @PageSize ) ) +' '+ @SelectColumn +'
141
FROM ['+ @TableName +']
142
WHERE '+ @KeyColumn +' >
143
(
144
SELECT Min('+ @KeyColumn +')
145
FROM
146
(
147
SELECT TOP '+ LTRIM( STR( @RecordCount - ((@AbsolutePage - 1) * @PageSize ) + 1) ) +'
148
'+ @KeyColumn +'
149
FROM ['+ @TableName +']
150
'+ dbo.iif(@SearchQuery, 'Where '+ @SearchQuery, '' ) +'
151
ORDER BY '+ @KeyColumn +' DESC
152
) As Child
153
)
154
'+ dbo.iif(@SearchQuery, ' AND '+ @SearchQuery, '' ) +'
155
ORDER BY '+ @KeyColumn +' ASC
156
'+ @ExpandQuery
157
Else
158
SET @SqlQuery = '
159
SELECT TOP '+ LTRIM( STR( @PageSize ) ) +' '+ @SelectColumn +'
160
FROM ['+ @TableName +']
161
WHERE '+ @KeyColumn +' <
162
(
163
SELECT MAX('+ @KeyColumn +')
164
FROM
165
(
166
SELECT TOP '+ LTRIM( STR( @RecordCount - ((@AbsolutePage - 1) * @PageSize ) + 1) ) +'
167
'+ @KeyColumn +'
168
FROM ['+ @TableName +']
169
'+ dbo.iif(@SearchQuery, 'Where '+ @SearchQuery, '' ) +'
170
ORDER BY '+ @KeyColumn +' ASC
171
) As Child
172
)
173
'+ dbo.iif(@SearchQuery, ' AND '+ @SearchQuery, '' ) +'
174
ORDER BY '+ @KeyColumn +' DESC
175
'+ @ExpandQuery
176![](/Images/OutliningIndicators/None.gif)
177
exec(@SqlQuery)
178
print(@SqlQuery)
179
print(DATEDIFF(millisecond, @Timer, getdate()))
180![](/Images/OutliningIndicators/None.gif)
181
GO
![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
3
![](/Images/OutliningIndicators/InBlock.gif)
4
![](/Images/OutliningIndicators/InBlock.gif)
5
![](/Images/OutliningIndicators/InBlock.gif)
6
![](/Images/OutliningIndicators/InBlock.gif)
7
![](/Images/OutliningIndicators/InBlock.gif)
8
![](/Images/OutliningIndicators/InBlock.gif)
9
![](/Images/OutliningIndicators/InBlock.gif)
10
![](/Images/OutliningIndicators/InBlock.gif)
11
![](/Images/OutliningIndicators/InBlock.gif)
12
![](/Images/OutliningIndicators/InBlock.gif)
13
![](/Images/OutliningIndicators/InBlock.gif)
14
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
15
![](/Images/OutliningIndicators/None.gif)
16
![](/Images/OutliningIndicators/None.gif)
17
![](/Images/OutliningIndicators/None.gif)
18
![](/Images/OutliningIndicators/None.gif)
19
![](/Images/OutliningIndicators/None.gif)
20
![](/Images/OutliningIndicators/None.gif)
21
![](/Images/OutliningIndicators/None.gif)
22
![](/Images/OutliningIndicators/None.gif)
23
![](/Images/OutliningIndicators/None.gif)
24
![](/Images/OutliningIndicators/None.gif)
25
![](/Images/OutliningIndicators/None.gif)
26
![](/Images/OutliningIndicators/None.gif)
27
![](/Images/OutliningIndicators/None.gif)
28
![](/Images/OutliningIndicators/None.gif)
29
![](/Images/OutliningIndicators/None.gif)
30
![](/Images/OutliningIndicators/None.gif)
31
![](/Images/OutliningIndicators/None.gif)
32
![](/Images/OutliningIndicators/None.gif)
33
![](/Images/OutliningIndicators/None.gif)
34
![](/Images/OutliningIndicators/None.gif)
35
![](/Images/OutliningIndicators/None.gif)
36
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
37
![](/Images/OutliningIndicators/InBlock.gif)
38
![](/Images/OutliningIndicators/InBlock.gif)
39
![](/Images/OutliningIndicators/InBlock.gif)
40
![](/Images/OutliningIndicators/InBlock.gif)
41
![](/Images/OutliningIndicators/InBlock.gif)
42
![](/Images/OutliningIndicators/InBlock.gif)
43
![](/Images/OutliningIndicators/InBlock.gif)
44
![](/Images/OutliningIndicators/InBlock.gif)
45
![](/Images/OutliningIndicators/InBlock.gif)
46
![](/Images/OutliningIndicators/InBlock.gif)
47
![](/Images/OutliningIndicators/InBlock.gif)
48
![](/Images/OutliningIndicators/InBlock.gif)
49
![](/Images/OutliningIndicators/InBlock.gif)
50
![](/Images/OutliningIndicators/InBlock.gif)
51
![](/Images/OutliningIndicators/InBlock.gif)
52
![](/Images/OutliningIndicators/InBlock.gif)
53
![](/Images/OutliningIndicators/InBlock.gif)
54
![](/Images/OutliningIndicators/InBlock.gif)
55
![](/Images/OutliningIndicators/InBlock.gif)
56
![](/Images/OutliningIndicators/InBlock.gif)
57
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
58
![](/Images/OutliningIndicators/None.gif)
59
![](/Images/OutliningIndicators/None.gif)
60
![](/Images/OutliningIndicators/None.gif)
61
![](/Images/OutliningIndicators/None.gif)
62
![](/Images/OutliningIndicators/None.gif)
63
![](/Images/OutliningIndicators/None.gif)
64
![](/Images/OutliningIndicators/None.gif)
65
![](/Images/OutliningIndicators/None.gif)
66
![](/Images/OutliningIndicators/None.gif)
67
![](/Images/OutliningIndicators/None.gif)
68
![](/Images/OutliningIndicators/None.gif)
69
![](/Images/OutliningIndicators/None.gif)
70
![](/Images/OutliningIndicators/None.gif)
71
![](/Images/OutliningIndicators/None.gif)
72
![](/Images/OutliningIndicators/None.gif)
73
![](/Images/OutliningIndicators/None.gif)
74
![](/Images/OutliningIndicators/None.gif)
75
![](/Images/OutliningIndicators/None.gif)
76
![](/Images/OutliningIndicators/None.gif)
77
![](/Images/OutliningIndicators/None.gif)
78
![](/Images/OutliningIndicators/None.gif)
79
![](/Images/OutliningIndicators/None.gif)
80
![](/Images/OutliningIndicators/None.gif)
81
![](/Images/OutliningIndicators/None.gif)
82
![](/Images/OutliningIndicators/None.gif)
83
![](/Images/OutliningIndicators/None.gif)
84
![](/Images/OutliningIndicators/None.gif)
85
![](/Images/OutliningIndicators/None.gif)
86
![](/Images/OutliningIndicators/None.gif)
87
![](/Images/OutliningIndicators/None.gif)
88
![](/Images/OutliningIndicators/None.gif)
89
![](/Images/OutliningIndicators/None.gif)
90
![](/Images/OutliningIndicators/None.gif)
91
![](/Images/OutliningIndicators/None.gif)
92
![](/Images/OutliningIndicators/None.gif)
93
![](/Images/OutliningIndicators/None.gif)
94
![](/Images/OutliningIndicators/None.gif)
95
![](/Images/OutliningIndicators/None.gif)
96
![](/Images/OutliningIndicators/None.gif)
97
![](/Images/OutliningIndicators/None.gif)
98
![](/Images/OutliningIndicators/None.gif)
99
![](/Images/OutliningIndicators/None.gif)
100
![](/Images/OutliningIndicators/None.gif)
101
![](/Images/OutliningIndicators/None.gif)
102
![](/Images/OutliningIndicators/None.gif)
103
![](/Images/OutliningIndicators/None.gif)
104
![](/Images/OutliningIndicators/None.gif)
105
![](/Images/OutliningIndicators/None.gif)
106
![](/Images/OutliningIndicators/None.gif)
107
![](/Images/OutliningIndicators/None.gif)
108
![](/Images/OutliningIndicators/None.gif)
109
![](/Images/OutliningIndicators/None.gif)
110
![](/Images/OutliningIndicators/None.gif)
111
![](/Images/OutliningIndicators/None.gif)
112
![](/Images/OutliningIndicators/None.gif)
113
![](/Images/OutliningIndicators/None.gif)
114
![](/Images/OutliningIndicators/None.gif)
115
![](/Images/OutliningIndicators/None.gif)
116
![](/Images/OutliningIndicators/None.gif)
117
![](/Images/OutliningIndicators/None.gif)
118
![](/Images/OutliningIndicators/None.gif)
119
![](/Images/OutliningIndicators/None.gif)
120
![](/Images/OutliningIndicators/None.gif)
121
![](/Images/OutliningIndicators/None.gif)
122
![](/Images/OutliningIndicators/None.gif)
123
![](/Images/OutliningIndicators/None.gif)
124
![](/Images/OutliningIndicators/None.gif)
125
![](/Images/OutliningIndicators/None.gif)
126
![](/Images/OutliningIndicators/None.gif)
127
![](/Images/OutliningIndicators/None.gif)
128
![](/Images/OutliningIndicators/None.gif)
129
![](/Images/OutliningIndicators/None.gif)
130
![](/Images/OutliningIndicators/None.gif)
131
![](/Images/OutliningIndicators/None.gif)
132
![](/Images/OutliningIndicators/None.gif)
133
![](/Images/OutliningIndicators/None.gif)
134
![](/Images/OutliningIndicators/None.gif)
135
![](/Images/OutliningIndicators/None.gif)
136
![](/Images/OutliningIndicators/None.gif)
137
![](/Images/OutliningIndicators/None.gif)
138
![](/Images/OutliningIndicators/None.gif)
139
![](/Images/OutliningIndicators/None.gif)
140
![](/Images/OutliningIndicators/None.gif)
141
![](/Images/OutliningIndicators/None.gif)
142
![](/Images/OutliningIndicators/None.gif)
143
![](/Images/OutliningIndicators/None.gif)
144
![](/Images/OutliningIndicators/None.gif)
145
![](/Images/OutliningIndicators/None.gif)
146
![](/Images/OutliningIndicators/None.gif)
147
![](/Images/OutliningIndicators/None.gif)
148
![](/Images/OutliningIndicators/None.gif)
149
![](/Images/OutliningIndicators/None.gif)
150
![](/Images/OutliningIndicators/None.gif)
151
![](/Images/OutliningIndicators/None.gif)
152
![](/Images/OutliningIndicators/None.gif)
153
![](/Images/OutliningIndicators/None.gif)
154
![](/Images/OutliningIndicators/None.gif)
155
![](/Images/OutliningIndicators/None.gif)
156
![](/Images/OutliningIndicators/None.gif)
157
![](/Images/OutliningIndicators/None.gif)
158
![](/Images/OutliningIndicators/None.gif)
159
![](/Images/OutliningIndicators/None.gif)
160
![](/Images/OutliningIndicators/None.gif)
161
![](/Images/OutliningIndicators/None.gif)
162
![](/Images/OutliningIndicators/None.gif)
163
![](/Images/OutliningIndicators/None.gif)
164
![](/Images/OutliningIndicators/None.gif)
165
![](/Images/OutliningIndicators/None.gif)
166
![](/Images/OutliningIndicators/None.gif)
167
![](/Images/OutliningIndicators/None.gif)
168
![](/Images/OutliningIndicators/None.gif)
169
![](/Images/OutliningIndicators/None.gif)
170
![](/Images/OutliningIndicators/None.gif)
171
![](/Images/OutliningIndicators/None.gif)
172
![](/Images/OutliningIndicators/None.gif)
173
![](/Images/OutliningIndicators/None.gif)
174
![](/Images/OutliningIndicators/None.gif)
175
![](/Images/OutliningIndicators/None.gif)
176
![](/Images/OutliningIndicators/None.gif)
177
![](/Images/OutliningIndicators/None.gif)
178
![](/Images/OutliningIndicators/None.gif)
179
![](/Images/OutliningIndicators/None.gif)
180
![](/Images/OutliningIndicators/None.gif)
181
![](/Images/OutliningIndicators/None.gif)
第一个存储过程 计算总记录
第二个存储过程 为选择记录部分
根据传入页码判断位置 实现三个逻辑
请求页 = 第一页
请求页 <= 总页/2
请求页 > 总页/2
并且针对排序方式做了优化
总记录统计部分消耗资源比较大 尽可能做冗余
具体参数作用 存储过程说明里已经做了说明 这里就不重复了