分页存储过程3
1
CREATE PROCEDURE P_newpager
2![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
@tblname VARCHAR(255), -- 表名
4![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
@strGetFields nvarchar(1000) = "*", -- 需要返回的列
6![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
@fldName varchar(255)='', -- 排序的字段名
8![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
@PageSize int = 10, -- 页尺寸
10![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
@PageIndex int = 1, -- 页码
12![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
@doCount bit = 0, -- 返回, 非0 值则返回记录总数
14![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
@OrderType bit = 0, -- 设置排序类型, 非0 值则降序
16![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
@strWhere varchar(1500) = '' -- 查询条件(注意: 不要加where)
18![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
AS
20![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
declare @strSQL varchar(5000) -- 主语句
22![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
23
declare @strTmp varchar(110) -- 临时变量
24![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
declare @strOrder varchar(400) -- 排序类型
26![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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)
![](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)