1 Create PROCEDURE [dbo].[up_DataPager]
2 @tblName varchar(255), -- 表名
3 @fldName varchar(255), -- 排序的字段名,一般为唯一标识
4 @strGetFields varchar(1000) = ' * ', -- 需要返回的列
5 @PageSize int = 10, -- 每页有多少条记录
6 @PageIndex int = 1, -- 第几页
7 @Count int output, -- 返回记录总数
8 @OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
9 @strWhere varchar(1500) = ' 1=1 ' -- 查询条件 (注意: 不要加 where,初始化为1=1使得查询条件永远不为空)
10 AS
11 declare @strSQL varchar(5000) -- 主语句
12 declare @strTmp varchar(110) -- 临时变量
13 declare @strOrder varchar(400) -- 排序类型
14 declare @sumsql nvarchar(3000) -- 记录总数
15 --执行总数统计。
16 set @sumsql = 'select @Count = count(*) from '+@tblname +' where '+@strwhere
17 exec sp_executesql @sumsql,N'@Count int output',@Count output-- 纪录总数
18 --如果@OrderType不是0，就执行降序
19 if @OrderType != 0
20 begin
21 set @strTmp = '<(select min'
22 set @strOrder = ' order by [' + @fldName +'] desc'
23 end
24 else
25 begin
26 set @strTmp = '>(select max'
27 set @strOrder = ' order by [' + @fldName +'] asc'
28 end
29 --如果是第一页就执行以下代码，加快执行速度
30 if @PageIndex = 1
31 set @strSQL ='select top ' + str(@PageSize) +' '+@strGetFields+ '
32 from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder
33 else
34 --以下代码赋予了@strSQL以真正执行的SQL代码
35 set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['
36 + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
37 + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
38 + @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
39 + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
40 exec (@strSQL)
41 GO
42
43 -- =============================================
44 -- 使用RowNumber分页
45 -- 参数
46 -- {
47 -- @SQL : 查询语句
48 -- @Order : 排序字段
49 -- @CurPage : 当前页
50 -- @PageRows : 每页大小
51 -- @TotalRecorder : 记录总数
52 -- }
53 -- =============================================
54 CREATE PROCEDURE [dbo].[up_DataPageRowNumber]
55 -- Add the parameters for the stored procedure here
56 @SQL Nvarchar(2000),
57 @Order Nvarchar(20),
58 @PageIndex int,
59 @PageSize int,
60 @TotalRecorder int output
61 AS
62 BEGIN
63 -- SET NOCOUNT ON added to prevent extra result sets from
64 SET NOCOUNT ON;
65 declare @ExceSQL nvarchar(4000)
66
67
68 --设置开始行号
69 declare @start_row_num AS int
70 SET @start_row_num = (@PageIndex - 1) * @PageSize + 1
71 --设置结束行号
72 declare @end_row_num int
73 set @end_row_num = @PageIndex * @PageSize
74
75 --设置标识语句
76 declare @RowNumber nvarchar(100)
77 set @RowNumber = ', ROW_NUMBER() OVER(ORDER BY ' + @Order + ') as RowNumber from '
78
79 set @SQL = Replace(@SQL,' from ',@RowNumber)
80
81 --获取记录总数
82 set @ExceSQL = 'select @TotalRecorder=max(RowNumber) from (' + @SQL + ') as tmp’
83
84 execute sp_executesql @ExceSQL,N'@TotalRecorder int output',@TotalRecorder output
85
86 --设置查询语句
87 set @ExceSQL = 'select * from (' + @SQL + ') as tmp where RowNumber between ' + Convert(nvarchar,@start_row_num)
88 + ' And ' + Convert(nvarchar, @end_row_num)
89
90 execute(@ExceSQL)
91
92 END
93 GO
94
95
96
97 /*********************************************************
98 * 作 用：数据分页(完整SQL分页存储过程（支持多表联接）)
99 * 使用说明：
100 1、单表调用方法：EXEC up_DataPagerCommon @tblName = 'ipa',@fldName = '*',@pageSize =50,@page = 6789,@fldSort = '',@Sort = 1,@strCondition = '',@ID = 'id',@Dist = 0,@pageCount = null,@Counts = NULL
101 2、多表调用方法：EXEC up_DataPagerCommon @tblName = 'Info LEFT JOIN InfoType it ON it.typeid=Info.tid',@fldName = 'info.*,it.tname',@pageSize =50,@page = 1806,@fldSort = '',@Sort = 0,@strCondition = '',@ID = 'id',@Dist = 0, @pageCount = null,@Counts = NULL
102 * 多表联合查询使用需注意：1、多表中的主键字段不能为相同的名称。2、多表中不能允许具有相同名称的字段，如果存在相同名称的字段你可以使用AS重命名
103 *********************************************************/
104 CREATE PROCEDURE [dbo].[up_DataPagerCommon]
105 (
106 @tblName nvarchar(200), ----要显示的表或多个表的连接
107 @fldName nvarchar(500) = '*', ----要显示的字段列表
108 @pageSize int = 10, ----每页显示的记录个数
109 @page int = 1, ----要显示那一页的记录
110 @fldSort nvarchar(200) = null, ----排序字段列表或条件
111 @Sort bit = 0, ----排序方法，1为升序，0为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如：' SortA Asc,SortB Desc,SortC ')
112 @strCondition nvarchar(1000) = null, ----查询条件,不需where
113 @ID nvarchar(150), ----主表的主键
114 @Dist bit = 0, ----是否添加查询字段的 DISTINCT 默认0不添加/1添加
115 @pageCount int = 1 output, ----查询结果分页后的总页数
116 @Counts int = 1 output ----查询到的记录数
117 )
118 AS
119 SET NOCOUNT ON
120 Declare @sqlTmp nvarchar(1000) ----存放动态生成的SQL语句
121 Declare @strTmp nvarchar(1000) ----存放取得查询结果总数的查询语句
122 Declare @strID nvarchar(1000) ----存放取得查询开头或结尾ID的查询语句
123
124 Declare @strSortType nvarchar(10) ----数据排序规则A
125 Declare @strFSortType nvarchar(10) ----数据排序规则B
126
127 Declare @SqlSelect nvarchar(50) ----对含有DISTINCT的查询进行SQL构造
128 Declare @SqlCounts nvarchar(50) ----对含有DISTINCT的总数查询进行SQL构造
129
130
131 if @Dist = 0
132 begin
133 set @SqlSelect = 'select '
134 set @SqlCounts = 'Count(0)'
135 end
136 else
137 begin
138 set @SqlSelect = 'select distinct '
139 set @SqlCounts = 'Count(DISTINCT '+@ID+')'
140 end
141
142
143 if @Sort=0
144 begin
145 set @strFSortType=' DESC '
146 set @strSortType=' DESC '
147 end
148 else
149 begin
150 set @strFSortType=' ASC '
151 set @strSortType=' ASC '
152 end
153
154 if(@fldSort is not null and @fldSort<>'')
155 begin
156 set @fldSort=','+@fldSort
157 end
158 else
159 begin
160 set @fldSort=' '
161 end
162
163 --------生成查询语句--------
164 --此处@strTmp为取得查询结果数量的语句
165 if @strCondition is null or @strCondition='' --没有设置显示条件
166 begin
167 set @sqlTmp = @fldName + ' From ' + @tblName
168 set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName
169 set @strID = ' From ' + @tblName
170 end
171 else
172 begin
173 set @sqlTmp = + @fldName + 'From ' + @tblName + ' where (1>0) ' + @strCondition
174 set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName + ' where (1>0) ' + @strCondition
175 set @strID = ' From ' + @tblName + ' where (1>0) ' + @strCondition
176 end
177
178 ----取得查询结果总数量-----
179 exec sp_executesql @strTmp,N'@Counts int out ',@Counts out
180 declare @tmpCounts int
181 if @Counts = 0
182 set @tmpCounts = 1
183 else
184 set @tmpCounts = @Counts
185
186 --取得分页总数
187 set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize
188
189 /**//**当前页大于总页数 取最后一页**/
190 if @page>@pageCount
191 set @page=@pageCount
192
193 --/*-----数据分页2分处理-------*/
194 declare @pageIndex int --总数/页大小
195 declare @lastcount int --总数%页大小
196
197 set @pageIndex = @tmpCounts/@pageSize
198 set @lastcount = @tmpCounts%@pageSize
199 if @lastcount > 0
200 set @pageIndex = @pageIndex + 1
201 else
202 set @lastcount = @pagesize
203
204 --为配合显示
205 --set nocount off
206 --select @page curpage,@pageSize pagesize,@pageCount countpage,@tmpCounts [Rowcount]
207 --set nocount on
208
209 --//***显示分页
210 if @strCondition is null or @strCondition='' --没有设置显示条件
211 begin
212 if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理
213 begin
214 if @page=1
215 set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(40))+' '+ @fldName+' from '+@tblName
216 +' order by '+ @ID+' '+ @strFSortType+@fldSort
217 else
218 begin
219 set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(40))+' '+ @fldName+' from '+@tblName
220 +' where '+@ID
221 if @Sort=0
222 set @strTmp = @strTmp + '>(select max('
223 else
224 set @strTmp = @strTmp + '<(select min('
225 set @strTmp = @strTmp + @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName
226 +' order by '+ @ID+' '+ @strFSortType+@fldSort+') AS TBMinID)'
227 +' order by '+ @ID+' '+ @strFSortType+@fldSort
228 end
229 end
230 else
231
232 begin
233 set @page = @pageIndex-@page+1 --后半部分数据处理
234 if @page <= 1 --最后一页数据显示
235 set @strTmp=@SqlSelect+' * from ('+@SqlSelect+'top '+ CAST(@lastcount as VARCHAR(40))+' '+ @fldName+' from '+@tblName
236 +' order by '+ @ID +' '+ @strSortType+@fldSort+') AS TempTB'+' order by '+ @ID+' '+ @strFSortType+@fldSort
237 else
238 begin
239 set @strTmp=@SqlSelect+' * from ('+@SqlSelect+'top '+ CAST(@pageSize as VARCHAR(40))+' '+ @fldName+' from '+@tblName
240 +' where '+@ID
241 if @Sort=0
242 set @strTmp=@strTmp+' <(select min('
243 else
244 set @strTmp=@strTmp+' >(select max('
245 set @strTmp=@strTmp+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName
246 +' order by '+ @ID +' '+ @strSortType+@fldSort+') AS TBMaxID)'
247 +' order by '+ @ID +' '+ @strSortType+@fldSort+') AS TempTB'+' order by '+ @ID+' '+ @strFSortType+@fldSort
248 end
249 end
250
251 end
252
253 else --有查询条件
254 begin
255 if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理
256 begin
257 if @page=1
258 set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(40))+' '+ @fldName+' from '+@tblName
259 +' where 1=1 ' + @strCondition + ' order by '+ @ID+' '+ @strFSortType+@fldSort
260 else
261 begin
262 set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(40))+' '+ @fldName+' from '+@tblName
263 +' where '+@ID
264 if @Sort=0
265 set @strTmp = @strTmp + '>(select max('
266 else
267 set @strTmp = @strTmp + '<(select min('
268
269 set @strTmp = @strTmp + @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName
270 +' where (1=1) ' + @strCondition +' order by '+ @ID+' '+ @strFSortType+@fldSort+') AS TBMinID)'
271 +' '+ @strCondition +' order by '+ @ID+' '+ @strFSortType+@fldSort
272 end
273 end
274 else
275 begin
276 set @page = @pageIndex-@page+1 --后半部分数据处理
277 if @page <= 1 --最后一页数据显示
278 set @strTmp=@SqlSelect+' * from ('+@SqlSelect+'top '+ CAST(@lastcount as VARCHAR(40))+' '+ @fldName+' from '+@tblName
279 +' where (1=1) '+ @strCondition +' order by '+ @ID +' '+ @strSortType+@fldSort+') AS TempTB'+' order by '+ @ID+' '+ @strFSortType+@fldSort
280 else
281 begin
282 set @strTmp=@SqlSelect+' * from ('+@SqlSelect+'top '+ CAST(@pageSize as VARCHAR(40))+' '+ @fldName+' from '+@tblName
283 +' where '+@ID
284 if @Sort=0
285 set @strTmp = @strTmp + '<(select min('
286 else
287 set @strTmp = @strTmp + '>(select max('
288 set @strTmp = @strTmp + @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName
289 +' where (1=1) '+ @strCondition +' order by '+ @ID +' '+ @strSortType+@fldSort+') AS TBMaxID)'
290 +' '+ @strCondition+' order by '+ @ID +' '+ @strSortType+@fldSort+') AS TempTB'+' order by '+ @ID+' '+ @strFSortType+@fldSort
291 end
292 end
293
294 end
295
296 ------返回查询结果-----
297 SET NOCOUNT off
298 exec sp_executesql @strTmp
299 print @strTmp
300
301 GO
浙公网安备 33010602011771号