1
CREATE
PROCEDURE
P_newpager
2![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
@tblname
VARCHAR
(
255
),
--
表名
4
5
@strGetFields
nvarchar
(
1000
)
=
"
*
",
--
需要返回的列
6
7
@fldName
varchar
(
255
)
=
''
,
--
排序的字段名
8
9
@PageSize
int
=
10
,
--
页尺寸
10
11
@PageIndex
int
=
1
,
--
页码
12
13
@doCount
bit
=
0
,
--
返回, 非0 值则返回记录总数
14
15
@OrderType
bit
=
0
,
--
设置排序类型, 非0 值则降序
16
17
@strWhere
varchar
(
1500
)
=
''
--
查询条件(注意: 不要加where)
18
19
AS
20![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
declare
@strSQL
varchar
(
5000
)
--
主语句
22
23
declare
@strTmp
varchar
(
110
)
--
临时变量
24
25
declare
@strOrder
varchar
(
400
)
--
排序类型
26
27
if
@doCount
!=
0
28![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
29
begin
30![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
31
if
@strWhere
!=
''
32![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
33
set
@strSQL
=
'
select count(*) as Total from [
'
+
@tblName
+
'
] where 1=1
'
+
@strWhere
34![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
35
else
36![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
37
set
@strSQL
=
'
select count(*) as Total from [
'
+
@tblName
+
'
]
'
38![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
39
end
--
以上代码的意思是如果@doCount传递过来的不是,就执行总数统计。以下的所有代码都是@doCount为的情况:
40
41
else
42![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
43
begin
44![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
45
if
@OrderType
!=
0
--
降序
46
47
begin
48![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
49
set
@strTmp
=
'
<(select min
'
50![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
51
set
@strOrder
=
'
order by [
'
+
@fldName
+
'
] desc
'
--
如果@OrderType不是0,就执行降序,这句很重要!
52
53
end
54![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
55
else
56![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
57
begin
58![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
59
set
@strTmp
=
'
>(select max
'
60![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
61
set
@strOrder
=
'
order by [
'
+
@fldName
+
'
] asc
'
62![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
63
end
64![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
65
if
@PageIndex
=
1
66![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
67
begin
68![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
69
if
@strWhere
!=
''
70![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
71
set
@strSQL
=
'
select top
'
+
str
(
@PageSize
)
+
'
'
+
@strGetFields
+
'
from [
'
+
@tblName
+
'
] where 1=1
'
+
@strWhere
+
'
'
+
@strOrder
72![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
73
else
74![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
75
set
@strSQL
=
'
select top
'
+
str
(
@PageSize
)
+
'
'
+
@strGetFields
+
'
from [
'
+
@tblName
+
'
]
'
+
@strOrder
--
如果是第一页就执行以上代码,这样会加快执行速度
76
77
end
78![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
79
else
80![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
81
begin
--
以下代码赋予了@strSQL以真正执行的SQL代码
82
83
set
@strSQL
=
'
select top
'
+
str
(
@PageSize
)
+
'
'
+
@strGetFields
+
'
from [
'
+
@tblName
+
'
] where [
'
+
@fldName
+
'
]
'
+
@strTmp
+
'
([
'
+
@fldName
+
'
]) from (select top
'
+
str
((
@PageIndex
-
1
)
*
@PageSize
)
+
'
[
'
+
@fldName
+
'
] from [
'
+
@tblName
+
'
]
'
+
@strOrder
+
'
) as tblTmp)
'
+
@strOrder
84![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
85
if
@strWhere
!=
''
86![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
87
set
@strSQL
=
'
select top
'
+
str
(
@PageSize
)
+
'
'
+
@strGetFields
+
'
from [
'
+
@tblName
+
'
] where [
'
+
@fldName
+
'
]
'
+
@strTmp
+
'
([
'
+
@fldName
+
'
]) from (select top
'
+
str
((
@PageIndex
-
1
)
*
@PageSize
)
+
'
[
'
+
@fldName
+
'
] from [
'
+
@tblName
+
'
] where 1=1
'
+
@strWhere
+
'
'
+
@strOrder
+
'
) as tblTmp) and 1=1
'
+
@strWhere
+
'
'
+
@strOrder
88![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
89
end
90![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
91
if
@strWhere
!=
''
--
得到记录的总行数
92
93
set
@strSQL
=
@strSQL
+
'
; select count(*) as Total from [
'
+
@tblName
+
'
] where 1=1
'
+
@strWhere
94![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
95
else
96![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
97
set
@strSQL
=
@strSQL
+
'
; select count(*) as Total from [
'
+
@tblName
+
'
]
'
98![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
99
end
100![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
101
exec
(
@strSQL
)
102![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
103
RETURN
104![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
105![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
106
GO
107
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
30
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
31
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
32
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
33
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
34
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
35
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
36
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
37
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
38
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
39
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
40
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
41
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
42
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
43
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
44
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
45
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
46
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
47
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
48
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
49
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
50
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
51
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
52
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
53
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
54
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
55
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
56
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
57
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
58
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
59
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
60
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
61
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
62
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
63
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
64
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
65
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
66
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
67
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
68
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
69
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
70
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
71
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
72
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
73
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
74
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
75
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
76
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
77
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
78
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
79
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
80
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
81
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
82
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
83
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
84
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
85
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
86
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
87
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
88
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
89
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
90
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
91
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
92
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
93
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
94
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
95
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
96
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
97
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
98
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
99
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
100
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
101
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
102
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
103
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
104
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
105
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
106
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
107
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)