1
--/*----- 对数据进行了2分处理使查询前半部分数据与查询后半部分数据性能相同 -------*/
2
--/*-----存储过程 分页处理 2005-04-21修改 添加Distinct查询功能-------*/
3
--/*-----存储过程 分页处理 2005-05-18修改 多字段排序规则问题-------*/
4
--/*-----存储过程 分页处理 2005-06-15修改 多字段排序修改-------*/
5
ALTER PROCEDURE dbo.proc_ListPage
6
(
7
@tblName nvarchar(200), ----要显示的表或多个表的连接
8
@fldName nvarchar(500) = '*', ----要显示的字段列表
9
@pageSize int = 1, ----每页显示的记录个数
10
@page int = 10, ----要显示那一页的记录
11
@pageCount int = 1 output, ----查询结果分页后的总页数
12
@Counts int = 1 output, ----查询到的记录数
13
@fldSort nvarchar(200) = null, ----排序字段列表或条件
14
@Sort bit = 0, ----排序方法,0为升序,1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如:' SortA Asc,SortB Desc,SortC ')
15
@strCondition nvarchar(1000) = null, ----查询条件,不需where
16
@ID nvarchar(150), ----主表的主键
17
@Dist bit = 0 ----是否添加查询字段的 DISTINCT 默认0不添加/1添加
18
)
19
AS
20
SET NOCOUNT ON
21
Declare @sqlTmp nvarchar(1000) ----存放动态生成的SQL语句
22
Declare @strTmp nvarchar(1000) ----存放取得查询结果总数的查询语句
23
Declare @strID nvarchar(1000) ----存放取得查询开头或结尾ID的查询语句
24
25
Declare @strSortType nvarchar(10) ----数据排序规则A
26
Declare @strFSortType nvarchar(10) ----数据排序规则B
27
28
Declare @SqlSelect nvarchar(50) ----对含有DISTINCT的查询进行SQL构造
29
Declare @SqlCounts nvarchar(50) ----对含有DISTINCT的总数查询进行SQL构造
30
31
32
if @Dist = 0
33
begin
34
set @SqlSelect = 'select '
35
set @SqlCounts = 'Count(*)'
36
end
37
else
38
begin
39
set @SqlSelect = 'select distinct '
40
set @SqlCounts = 'Count(DISTINCT '+@ID+')'
41
end
42
43
44
if @Sort=0
45
begin
46
set @strFSortType=' ASC '
47
set @strSortType=' DESC '
48
end
49
else
50
begin
51
set @strFSortType=' DESC '
52
set @strSortType=' ASC '
53
end
54
55
56
57
--------生成查询语句--------
58
--此处@strTmp为取得查询结果数量的语句
59
if @strCondition is null or @strCondition='' --没有设置显示条件
60
begin
61
set @sqlTmp = @fldName + ' From ' + @tblName
62
set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName
63
set @strID = ' From ' + @tblName
64
end
65
else
66
begin
67
set @sqlTmp = + @fldName + 'From ' + @tblName + ' where (1>0) ' + @strCondition
68
set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName + ' where (1>0) ' + @strCondition
69
set @strID = ' From ' + @tblName + ' where (1>0) ' + @strCondition
70
end
71
72
----取得查询结果总数量-----
73
exec sp_executesql @strTmp,N'@Counts int out ',@Counts out
74
declare @tmpCounts int
75
if @Counts = 0
76
set @tmpCounts = 1
77
else
78
set @tmpCounts = @Counts
79
80
--取得分页总数
81
set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize
82
83
/**//**//**//**当前页大于总页数 取最后一页**/
84
if @page>@pageCount
85
set @page=@pageCount
86
87
--/*-----数据分页2分处理-------*/
88
declare @pageIndex int --总数/页大小
89
declare @lastcount int --总数%页大小
90
91
set @pageIndex = @tmpCounts/@pageSize
92
set @lastcount = @tmpCounts%@pageSize
93
if @lastcount > 0
94
set @pageIndex = @pageIndex + 1
95
else
96
set @lastcount = @pagesize
97
98
--//***显示分页
99
if @strCondition is null or @strCondition='' --没有设置显示条件
100
begin
101
if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理
102
begin
103
set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
104
+' where '+@ID+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName
105
+' order by '+ @fldSort +' '+ @strFSortType+')'
106
+' order by '+ @fldSort +' '+ @strFSortType
107
end
108
else
109
begin
110
set @page = @pageIndex-@page+1 --后半部分数据处理
111
if @page <= 1 --最后一页数据显示
112
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' from '+@tblName
113
+' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
114
else
115
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
116
+' where '+@ID+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName
117
+' order by '+ @fldSort +' '+ @strSortType+')'
118![](/Images/OutliningIndicators/None.gif)
119
+' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
120
end
121
end
122![](/Images/OutliningIndicators/None.gif)
123
else --有查询条件
124
begin
125
if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理
126
begin
127
set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName +' from '+@tblName
128
+' where '+@ID+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName
129
+' Where (1>0) ' + @strCondition + ' order by '+ @fldSort +' '+ @strFSortType+')'
130
+' ' + @strCondition + ' order by '+ @fldSort +' '+ @strFSortType
131
end
132
else
133
begin
134
set @page = @pageIndex-@page+1 --后半部分数据处理
135
if @page <= 1 --最后一页数据显示
136
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' from '+@tblName
137
+' where (1>0) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
138
else
139
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
140
+' where '+@ID+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName
141
+' where (1>0) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+')'
142
+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
143
end
144
end
145![](/Images/OutliningIndicators/None.gif)
146
------返回查询结果-----
147
exec sp_executesql @strTmp
148
--print @strTmp
149
SET NOCOUNT OFF
![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/None.gif)
3
![](/Images/OutliningIndicators/None.gif)
4
![](/Images/OutliningIndicators/None.gif)
5
![](/Images/OutliningIndicators/None.gif)
6
![](/Images/OutliningIndicators/None.gif)
7
![](/Images/OutliningIndicators/None.gif)
8
![](/Images/OutliningIndicators/None.gif)
9
![](/Images/OutliningIndicators/None.gif)
10
![](/Images/OutliningIndicators/None.gif)
11
![](/Images/OutliningIndicators/None.gif)
12
![](/Images/OutliningIndicators/None.gif)
13
![](/Images/OutliningIndicators/None.gif)
14
![](/Images/OutliningIndicators/None.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/None.gif)
37
![](/Images/OutliningIndicators/None.gif)
38
![](/Images/OutliningIndicators/None.gif)
39
![](/Images/OutliningIndicators/None.gif)
40
![](/Images/OutliningIndicators/None.gif)
41
![](/Images/OutliningIndicators/None.gif)
42
![](/Images/OutliningIndicators/None.gif)
43
![](/Images/OutliningIndicators/None.gif)
44
![](/Images/OutliningIndicators/None.gif)
45
![](/Images/OutliningIndicators/None.gif)
46
![](/Images/OutliningIndicators/None.gif)
47
![](/Images/OutliningIndicators/None.gif)
48
![](/Images/OutliningIndicators/None.gif)
49
![](/Images/OutliningIndicators/None.gif)
50
![](/Images/OutliningIndicators/None.gif)
51
![](/Images/OutliningIndicators/None.gif)
52
![](/Images/OutliningIndicators/None.gif)
53
![](/Images/OutliningIndicators/None.gif)
54
![](/Images/OutliningIndicators/None.gif)
55
![](/Images/OutliningIndicators/None.gif)
56
![](/Images/OutliningIndicators/None.gif)
57
![](/Images/OutliningIndicators/None.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/ExpandedBlockStart.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)