1
2
3 -- 通用分页查询存储过程
4 -- exec p_PageRecordset_Table '#TempTable',20,1,100,0
5 -- ------------------------------------------------------------
6 CREATE PROCEDURE p_PageRecordset_Table
7 (
8 @TableName NVARCHAR ( 100 ), -- 全局临时表名称
9 @PageSize INT , -- 每页的记录条数
10 @PageNumber INT , -- 当前页面
11 @TotalSize INT , -- 总记录条数
12 @TotalPage INT OUTPUT -- 总页数
13 )
14 AS
15 BEGIN
16
17 SET NOCOUNT ON
18 DECLARE @SqlText AS NVARCHAR ( 1000 )
19
20 -- 计算总页数
21 IF @PageSize > 0
22 BEGIN
23 SET @TotalPage = @TotalSize / @PageSize
24 IF ( @PageSize * @TotalPage ) <> @TotalSize
25 SET @TotalPage = @TotalPage + 1
26 END
27 ELSE
28 BEGIN
29 SET @TotalPage = 1
30 END
31
32 -- 校正输入参数
33 IF @PageNumber < 1 SET @PageNumber = 1
34 IF @PageNumber > @TotalPage SET @PageNumber = @TotalPage
35
36 IF @PageSize <= 0 OR @TotalSize = 0
37 BEGIN
38 -- 如果设置PageSize小于等于0,表示不分页。
39 SET @SqlText = '
40 SELECT * FROM ' + @TableName + '
41 DROP TABLE ' + @TableName
42 END
43 ELSE
44 BEGIN
45 SET @SqlText = '
46 SET NOCOUNT ON
47
48 SELECT * FROM ' + @TableName + '
49 WHERE
50 ' + CAST (( @PageNumber - 1 ) * @PageSize + 1 AS NVARCHAR ( 30 )) + ' <=ROWNUM and ROWNUM<= ' + CAST ( @PageNumber * @PageSize AS NVARCHAR ( 30 )) + '
51 ORDER BY ROWNUM
52
53 DROP TABLE ' + @TableName
54 END
55
56 -- PRINT @SqlText
57 EXEC ( @SqlText )
58
59 End
60 GO
61
62
63 -------------------------------------
64 调用实例:
65
66
67 DECLARE @PageSize INT
68 DECLARE @PageNumber INT
69 DECLARE @TotalSize INT
70 DECLARE @TotalPage INT
71
72 SET @PageSize = 5
73 SET @PageNumber = 1
74
75 SET NOCOUNT ON
76
77 -- 把查询的结果放到一个临时表中,供分页处理。
78 SELECT
79 CAST (EmployeeID as INT ) AS EmployeeID, -- 如果源表中有自编号的字段,要转换一下。
80 LastName,
81 FirstName,
82 BirthDate,
83 Address,
84 Region,
85 PostalCode,
86 Country,
87 IDENTITY ( INT , 1 , 1 ) AS ROWNUM -- 添加一个新的编号列,供分页存储过程使用。
88 INTO #tbl_p_Page_List -- 可以随机生成一个临时表名
89 FROM Employees
90 ORDER BY LastName,FirstName
91
92
93
94 -- ------------------------------------------------
95 -- 以下是通用的分页过程
96 -- ------------------------------------------------
97 -- 取得总记录条数
98 SELECT @TotalSize = @@ROWCOUNT
99
100 -- 调用通用的分页处理过程
101 -- 注意:临时表中必须有自动编号的字段 IDENTITY(INT,1,1) AS ROWNUM
102 EXEC p_PageRecordset_Table ' #tbl_p_Page_List ' ,
103 @PageSize , @PageNumber , @TotalSize , @TotalPage OUTPUT
104 -- ------------------------------------------------
105
106 PRINT @TotalSize
107 PRINT @TotalPage
108
109 GO
110
111
112
113
114
115
116 下面是简单的一个用于分页的存储过程:
117
118 create proc articlePages
119 @pageIndex int , -- 页数
120 @pageSize int , -- 页面显示的数据量
121 @dbFields varchar ( 1000 ) -- 查询的列名
122 as
123 declare @strSql varchar ( 5000 ) -- 查询的Sql语句
124
125 if @pageIndex = ' 1 '
126 set @strSql = ' select top ' + ' ' + str ( @pageSize ) + + ' ' + @dbFields + ' from article order by id desc '
127 else
128 begin
129 set @strSql = ' select top ' + ' ' + str ( @pageSize ) + ' ' + @dbFields + ' ' + ' from article '
130 set @strSql = @strSql + ' ' + ' where id <( '
131 set @strSql = @strSql + ' select min(id) from ( '
132 set @strSql = @strSql + ' select top ' + str (( @pageIndex - 1 ) * @pageSize ) + + ' id from article order by id desc) as t ' + ' ) order by id desc '
133 end
134
135 exec ( @strSql )
136
137
138
139 这个存储过程的缺点是没有查询条件,并且怎么找到最后一页是多少作为参数传入呀?
140
141
142
143
144
145
146
147
148
149 还有一个牛的号称千万级
150
151 参数说明:
152 1 .Tables :表名称,视图
153 2 .PrimaryKey :主关键字
154 3 .Sort :排序语句,不带Order By 比如:NewsID Desc ,OrderRows Asc
155 4 .CurrentPage :当前页码
156 5 .PageSize :分页尺寸
157 6 .Filter :过滤语句,不带Where
158 7 . Group :Group语句,不带Group By
159 效果演示:http: // www.cn5135.com / _App / Enterprise / QueryResult.aspx
160 ***************************************************************/
161 (
162 @Tables varchar ( 1000 ),
163 @PrimaryKey varchar ( 100 ),
164 @Sort varchar ( 200 ) = NULL ,
165 @CurrentPage int = 1 ,
166 @PageSize int = 10 ,
167 @Fields varchar ( 1000 ) = ' * ' ,
168 @Filter varchar ( 1000 ) = NULL ,
169 @Group varchar ( 1000 ) = NULL
170 )
171 AS
172 /* 默认排序 */
173 IF @Sort IS NULL or @Sort = ''
174 SET @Sort = @PrimaryKey
175 DECLARE @SortTable varchar ( 100 )
176 DECLARE @SortName varchar ( 100 )
177 DECLARE @strSortColumn varchar ( 200 )
178 DECLARE @operator char ( 2 )
179 DECLARE @type varchar ( 100 )
180 DECLARE @prec int
181 /* 设定排序语句. */
182 IF CHARINDEX ( ' DESC ' , @Sort ) > 0
183 BEGIN
184 SET @strSortColumn = REPLACE ( @Sort , ' DESC ' , '' )
185 SET @operator = ' <= '
186 END
187 ELSE
188 BEGIN
189 IF CHARINDEX ( ' ASC ' , @Sort ) = 0
190 SET @strSortColumn = REPLACE ( @Sort , ' ASC ' , '' )
191 SET @operator = ' >= '
192 END
193 IF CHARINDEX ( ' . ' , @strSortColumn ) > 0
194 BEGIN
195 SET @SortTable = SUBSTRING ( @strSortColumn , 0 , CHARINDEX ( ' . ' , @strSortColumn ))
196 SET @SortName = SUBSTRING ( @strSortColumn , CHARINDEX ( ' . ' , @strSortColumn ) + 1 , LEN ( @strSortColumn ))
197 END
198 ELSE
199 BEGIN
200 SET @SortTable = @Tables
201 SET @SortName = @strSortColumn
202 END
203 Select @type = t.name, @prec = c.prec
204 FROM sysobjects o
205 JOIN syscolumns c on o.id = c.id
206 JOIN systypes t on c.xusertype = t.xusertype
207 Where o.name = @SortTable AND c.name = @SortName
208 IF CHARINDEX ( ' char ' , @type ) > 0
209 SET @type = @type + ' ( ' + CAST ( @prec AS varchar ) + ' ) '
210 DECLARE @strPageSize varchar ( 50 )
211 DECLARE @strStartRow varchar ( 50 )
212 DECLARE @strFilter varchar ( 1000 )
213 DECLARE @strSimpleFilter varchar ( 1000 )
214 DECLARE @strGroup varchar ( 1000 )
215 /* 默认当前页 */
216 IF @CurrentPage < 1
217 SET @CurrentPage = 1
218 /* 设置分页参数. */
219 SET @strPageSize = CAST ( @PageSize AS varchar ( 50 ))
220 SET @strStartRow = CAST ((( @CurrentPage - 1 ) * @PageSize + 1 ) AS varchar ( 50 ))
221 /* 筛选以及分组语句. */
222
223 IF @Filter IS NOT NULL AND @Filter != ''
224 BEGIN
225 SET @strFilter = ' Where ' + @Filter + ' '
226 SET @strSimpleFilter = ' AND ' + @Filter + ' '
227 END
228 ELSE
229 BEGIN
230 SET @strSimpleFilter = ''
231 SET @strFilter = ''
232 END
233 IF @Group IS NOT NULL AND @Group != ''
234 SET @strGroup = ' GROUP BY ' + @Group + ' '
235 ELSE
236 SET @strGroup = ''
237 /* 执行查询语句 */
238 EXEC (
239 '
240 DECLARE @SortColumn ' + @type + '
241 SET ROWCOUNT ' + @strStartRow + '
242 Select @SortColumn= ' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' orDER BY ' + @Sort + '
243 SET ROWCOUNT ' + @strPageSize + '
244 Select ' + @Fields + ' FROM ' + @Tables + ' Where ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' orDER BY ' + @Sort + '
245 '
246 )
247 GO
通用分页查询存储过程
最新推荐文章于 2021-02-07 11:28:24 发布