前段时间由于项目需求想上网搜个通用分页存储过程先用着
本以为非常简单的事竟然让我大失所望
竟然没有一个能够满足我的需求,不是效率太低就是限制太多不符合实际需要
bug也是一顿狂冒,更有甚者执行都执行不了
我想应该很多朋友都有这样的遭遇吧
于是一气之下决定自己研究
在众多分析性能的文章中,很庆幸翻到了这一篇http://www.codeproject.com/aspnet/PagingLarge.asp(感谢园子里一位朋友的提示,具体名字已经不记得了),对分页存储过程有兴趣的朋友可以去看,写的很详尽,随便PF一下国外同行的认真和专业.
在上面提到的那篇文章的一开始大力推崇使用RowCoun的t方法,
可是由于原文中提供的方法不支持非unique字段的排序
大多场合都并不适用
文章末尾连作者自己提到,已经开始考虑改用cursor方法
可我对RowCount方法算是情有独中,于是对原文中该方法进行了改进
改进后的方法已基本上满足我的需要,现在发布出来,有用的朋友可以下载试用
修改记录:
1)增加对非unique字段排序的支持,但必须设定一个PK字段(注:只要是unique字段都可以作为pk字段)
2)增加记录总数输出参数
3)修改原过程若干BUG
4)修改PK字段只能是int型的bug(刚发现的,^_^)
注:
1)如表名参数为多表连接时,sort列必须指定表名;
2)只支持单字段排序,有朋友如果问为什么不做成可以多字段排序的,理论上确实有这种可能性,但需要以一定的效率损失为代价,而且会使方法过于复杂,如真有这种需要,完全可以写一个单独的分页存储过程,无论在性能还是复杂度上都比通用要简单.
3)由于时间原因没有大量测试,如有BUG,请您提出,立刻修正;
4)由于时间关系,只写了一个简单示例,需要的朋友可以下载
分页示例
1
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Paging_RowCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
2drop procedure [dbo].[Paging_RowCount]
3GO
4
5SET QUOTED_IDENTIFIER ON
6GO
7SET ANSI_NULLS ON
8GO
9---------------------------------------------------------------
10-- 分页存储过程(使用RowCount) --edit by SiBen
11-- summary:
12-- 获取表或表集合的分页数据
13-- 当多表连接时,sort列必须指定表名
14---------------------------------------------------------------
15
16CREATE PROCEDURE Paging_RowCount
17(
18@Tables varchar(1000),
19@PK varchar(100),
20@Sort varchar(200) = NULL,
21@PageNumber int = 1,
22@PageSize int = 10,
23@Fields varchar(1000) = '*',
24@Filter varchar(1000) = NULL,
25@Group varchar(1000) = NULL,
26@RecordCount int = 0 output
27)
28AS
29
30
/*Default Sorting*/
31IF @Sort IS NULL OR @Sort = ''
32 SET @Sort = @PK
33
34/*Find the @PK type*/
35DECLARE @SortTable varchar(100)
36DECLARE @SortName varchar(100)
37DECLARE @PKTable varchar(100)
38DECLARE @PKName varchar(100)
39DECLARE @strSortColumn varchar(200)
40DECLARE @operator char(2)
41DECLARE @type varchar(100)
42DECLARE @prec int
43
44/*Set sorting variables.*/
45IF CHARINDEX('DESC',@Sort)>0
46 BEGIN
47 SET @strSortColumn = REPLACE(@Sort, 'DESC', '')
48 SET @operator = '<'
49 END
50ELSE
51 BEGIN
52 IF CHARINDEX('ASC', @Sort) > 0
53 SET @strSortColumn = REPLACE(@Sort, 'ASC', '')
54 ELSE
55 SET @strSortColumn = @Sort
56
57 SET @operator = '>'
58 END
59
60/* Set PK,Sort name */
61IF CHARINDEX('.', @strSortColumn) > 0
62 BEGIN
63 SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
64 SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))
65 END
66ELSE
67 BEGIN
68 SET @SortTable = @Tables
69 SET @SortName = @strSortColumn
70 END
71IF CHARINDEX('.', @PK) > 0
72 BEGIN
73 SET @PKTable = SUBSTRING(@PK, 0, CHARINDEX('.',@PK))
74 SET @PKName = SUBSTRING(@PK, CHARINDEX('.',@PK) + 1, LEN(@PK))
75 END
76ELSE
77 BEGIN
78 SET @PKTable = @Tables
79 SET @PKName = @PK
80 END
81
82SELECT @type=t.name, @prec=c.prec
83FROM sysobjects o
84JOIN syscolumns c on o.id=c.id
85JOIN systypes t on c.xusertype=t.xusertype
86WHERE o.name = @SortTable AND c.name = @SortName
87
88IF CHARINDEX('char', @type) > 0
89 SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
90
91DECLARE @strPageSize varchar(50)
92DECLARE @strStartRow varchar(50)
93DECLARE @strFilter varchar(1000)
94DECLARE @strSimpleFilter varchar(1000)
95DECLARE @strGroup varchar(1000)
96
97/*Default Page Number*/
98IF @PageNumber < 1
99 SET @PageNumber = 1
100
101/*Set paging variables.*/
102SET @strPageSize = CAST(@PageSize AS varchar(50))
103SET @strStartRow = CAST(((@PageNumber - 1)*@PageSize + 1) AS varchar(50))
104
105/*Set filter & group variables.*/
106IF @Filter IS NOT NULL AND @Filter != ''
107 BEGIN
108 SET @strFilter = ' WHERE ' + @Filter + ' '
109 SET @strSimpleFilter = ' AND ' + @Filter + ' '
110 END
111ELSE
112 BEGIN
113 SET @strSimpleFilter = ''
114 SET @strFilter = ''
115 END
116IF @Group IS NOT NULL AND @Group != ''
117 SET @strGroup = ' GROUP BY ' + @Group + ' '
118ELSE
119 SET @strGroup = ''
120
121/*Get rows count.*/
122DECLARE @str_Count_SQL nvarchar(500)
123SET @str_Count_SQL= 'SELECT @TotalCount=count(*) FROM ' + @Tables + @strFilter
124EXEC sp_executesql @str_Count_SQL,N'@TotalCount int=0 output',@RecordCount output
125
126/*Execute dynamic query*/
127IF @PKTable = @SortTable and @PKName = @SortName
128 BEGIN
129 EXEC(
130 '
131 DECLARE @SortColumn ' + @type + '
132 SET ROWCOUNT ' + @strStartRow + '
133 SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
134 SET ROWCOUNT ' + @strPageSize + '
135 SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + '= @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
136 '
137 )
138 END
139ELSE
140 BEGIN
141 /* Get PK Type */
142 DECLARE @pktype varchar(100)
143 DECLARE @pkprec int
144
145 SELECT @pktype=t.name, @pkprec=c.prec
146 FROM sysobjects o
147 JOIN syscolumns c on o.id=c.id
148 JOIN systypes t on c.xusertype=t.xusertype
149 WHERE o.name = @PKTable AND c.name = @PKName
150
151 IF CHARINDEX('char', @pktype) > 0
152 SET @pktype = @pktype + '(' + CAST(@pkprec AS varchar) + ')'
153
154 /*Execute dynamic query*/
155 EXEC(
156 '
157 DECLARE @SortColumn ' + @type + '
158 DECLARE @SortNullValue ' + @type + '
159 DECLARE @PKStartValue ' + @pktype + '
160 SET @SortNullValue=CAST('''' as '+ @type +')
161 SET ROWCOUNT ' + @strStartRow + '
162 SELECT @SortColumn= isNull(' + @strSortColumn + ',@SortNullValue), @PKStartValue = '+ @PK +' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ','+ @PK +' Desc
163 SET ROWCOUNT ' + @strPageSize + '
164 SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE (isNull(' + @strSortColumn+',@SortNullValue)' + @operator + ' @SortColumn or (isNull(' + @strSortColumn+',@SortNullValue)=@SortColumn and '+ @PK +'<=@PKStartValue))' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ','+ @PK +' Desc
165 '
166 )
167 END
168GO
169SET QUOTED_IDENTIFIER OFF
170GO
171SET ANSI_NULLS ON
172GO
173
174
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Paging_RowCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)2
drop procedure [dbo].[Paging_RowCount]3
GO4
5
SET QUOTED_IDENTIFIER ON 6
GO7
SET ANSI_NULLS ON 8
GO9
---------------------------------------------------------------10
-- 分页存储过程(使用RowCount) --edit by SiBen11
-- summary:12
-- 获取表或表集合的分页数据13
-- 当多表连接时,sort列必须指定表名 14
---------------------------------------------------------------15
16
CREATE PROCEDURE Paging_RowCount17
(18
@Tables varchar(1000),19
@PK varchar(100),20
@Sort varchar(200) = NULL,21
@PageNumber int = 1,22
@PageSize int = 10,23
@Fields varchar(1000) = '*',24
@Filter varchar(1000) = NULL,25
@Group varchar(1000) = NULL,26
@RecordCount int = 0 output 27
)28
AS29
30
/*Default Sorting*/
31
IF @Sort IS NULL OR @Sort = ''32
SET @Sort = @PK33
34
/*Find the @PK type*/35
DECLARE @SortTable varchar(100)36
DECLARE @SortName varchar(100)37
DECLARE @PKTable varchar(100)38
DECLARE @PKName varchar(100)39
DECLARE @strSortColumn varchar(200)40
DECLARE @operator char(2)41
DECLARE @type varchar(100)42
DECLARE @prec int43
44
/*Set sorting variables.*/ 45
IF CHARINDEX('DESC',@Sort)>046
BEGIN47
SET @strSortColumn = REPLACE(@Sort, 'DESC', '')48
SET @operator = '<'49
END50
ELSE51
BEGIN52
IF CHARINDEX('ASC', @Sort) > 053
SET @strSortColumn = REPLACE(@Sort, 'ASC', '')54
ELSE55
SET @strSortColumn = @Sort56
57
SET @operator = '>'58
END59
60
/* Set PK,Sort name */61
IF CHARINDEX('.', @strSortColumn) > 062
BEGIN63
SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))64
SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))65
END66
ELSE67
BEGIN68
SET @SortTable = @Tables69
SET @SortName = @strSortColumn70
END71
IF CHARINDEX('.', @PK) > 072
BEGIN73
SET @PKTable = SUBSTRING(@PK, 0, CHARINDEX('.',@PK))74
SET @PKName = SUBSTRING(@PK, CHARINDEX('.',@PK) + 1, LEN(@PK))75
END76
ELSE77
BEGIN78
SET @PKTable = @Tables79
SET @PKName = @PK80
END81
82
SELECT @type=t.name, @prec=c.prec83
FROM sysobjects o 84
JOIN syscolumns c on o.id=c.id85
JOIN systypes t on c.xusertype=t.xusertype86
WHERE o.name = @SortTable AND c.name = @SortName87
88
IF CHARINDEX('char', @type) > 089
SET @type = @type + '(' + CAST(@prec AS varchar) + ')'90
91
DECLARE @strPageSize varchar(50)92
DECLARE @strStartRow varchar(50)93
DECLARE @strFilter varchar(1000)94
DECLARE @strSimpleFilter varchar(1000)95
DECLARE @strGroup varchar(1000)96
97
/*Default Page Number*/98
IF @PageNumber < 199
SET @PageNumber = 1100
101
/*Set paging variables.*/102
SET @strPageSize = CAST(@PageSize AS varchar(50))103
SET @strStartRow = CAST(((@PageNumber - 1)*@PageSize + 1) AS varchar(50))104
105
/*Set filter & group variables.*/106
IF @Filter IS NOT NULL AND @Filter != ''107
BEGIN108
SET @strFilter = ' WHERE ' + @Filter + ' '109
SET @strSimpleFilter = ' AND ' + @Filter + ' '110
END111
ELSE112
BEGIN113
SET @strSimpleFilter = ''114
SET @strFilter = ''115
END116
IF @Group IS NOT NULL AND @Group != ''117
SET @strGroup = ' GROUP BY ' + @Group + ' '118
ELSE119
SET @strGroup = ''120
121
/*Get rows count.*/122
DECLARE @str_Count_SQL nvarchar(500)123
SET @str_Count_SQL= 'SELECT @TotalCount=count(*) FROM ' + @Tables + @strFilter124
EXEC sp_executesql @str_Count_SQL,N'@TotalCount int=0 output',@RecordCount output125
126
/*Execute dynamic query*/ 127
IF @PKTable = @SortTable and @PKName = @SortName128
BEGIN 129
EXEC(130
'131
DECLARE @SortColumn ' + @type + '132
SET ROWCOUNT ' + @strStartRow + '133
SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '134
SET ROWCOUNT ' + @strPageSize + '135
SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + '= @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '136
'137
)138
END139
ELSE140
BEGIN141
/* Get PK Type */142
DECLARE @pktype varchar(100)143
DECLARE @pkprec int144
145
SELECT @pktype=t.name, @pkprec=c.prec146
FROM sysobjects o 147
JOIN syscolumns c on o.id=c.id148
JOIN systypes t on c.xusertype=t.xusertype149
WHERE o.name = @PKTable AND c.name = @PKName150
151
IF CHARINDEX('char', @pktype) > 0152
SET @pktype = @pktype + '(' + CAST(@pkprec AS varchar) + ')'153
154
/*Execute dynamic query*/ 155
EXEC(156
'157
DECLARE @SortColumn ' + @type + '158
DECLARE @SortNullValue ' + @type + '159
DECLARE @PKStartValue ' + @pktype + '160
SET @SortNullValue=CAST('''' as '+ @type +')161
SET ROWCOUNT ' + @strStartRow + '162
SELECT @SortColumn= isNull(' + @strSortColumn + ',@SortNullValue), @PKStartValue = '+ @PK +' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ','+ @PK +' Desc 163
SET ROWCOUNT ' + @strPageSize + '164
SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE (isNull(' + @strSortColumn+',@SortNullValue)' + @operator + ' @SortColumn or (isNull(' + @strSortColumn+',@SortNullValue)=@SortColumn and '+ @PK +'<=@PKStartValue))' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ','+ @PK +' Desc165
'166
)167
END168
GO169
SET QUOTED_IDENTIFIER OFF 170
GO171
SET ANSI_NULLS ON 172
GO173
174
