通用分页查询存储过程

来源：互联网发布：dyndns免费域名注册编辑：程序博客网时间：2024/05/21 09:59

  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
118create   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参数说明:
1521.Tables :表名称,视图
1532.PrimaryKey :主关键字
1543.Sort :排序语句，不带Order By 比如：NewsID Desc,OrderRows Asc
1554.CurrentPage :当前页码
1565.PageSize :分页尺寸
1576.Filter :过滤语句，不带Where
1587.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)
171AS
172/**//*默认排序*/
173IF @Sort IS NULL or @Sort = ''
174SET @Sort = @PrimaryKey
175DECLARE @SortTable varchar(100)
176DECLARE @SortName varchar(100)
177DECLARE @strSortColumn varchar(200)
178DECLARE @operator char(2)
179DECLARE @type varchar(100)
180DECLARE @prec int
181/**//*设定排序语句.*/
182IF CHARINDEX('DESC',@Sort)>0
183BEGIN
184SET @strSortColumn = REPLACE(@Sort, 'DESC', '')
185SET @operator = '<='
186END
187ELSE
188BEGIN
189IF CHARINDEX('ASC', @Sort) = 0
190SET @strSortColumn = REPLACE(@Sort, 'ASC', '')
191SET @operator = '>='
192END
193IF CHARINDEX('.', @strSortColumn) > 0
194BEGIN
195SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
196SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))
197END
198ELSE
199BEGIN
200SET @SortTable = @Tables
201SET @SortName = @strSortColumn
202END
203Select @type=t.name, @prec=c.prec
204FROM sysobjects o
205JOIN syscolumns c on o.id=c.id
206JOIN systypes t on c.xusertype=t.xusertype
207Where o.name = @SortTable AND c.name = @SortName
208IF CHARINDEX('char', @type) > 0
209SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
210DECLARE @strPageSize varchar(50)
211DECLARE @strStartRow varchar(50)
212DECLARE @strFilter varchar(1000)
213DECLARE @strSimpleFilter varchar(1000)
214DECLARE @strGroup varchar(1000)
215/**//*默认当前页*/
216IF @CurrentPage < 1
217SET @CurrentPage = 1
218/**//*设置分页参数.*/
219SET @strPageSize = CAST(@PageSize AS varchar(50))
220SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(50))
221/**//*筛选以及分组语句.*/
222
223IF @Filter IS NOT NULL AND @Filter != ''
224BEGIN
225SET @strFilter = ' Where ' + @Filter + ' '
226SET @strSimpleFilter = ' AND ' + @Filter + ' '
227END
228ELSE
229BEGIN
230SET @strSimpleFilter = ''
231SET @strFilter = ''
232END
233IF @Group IS NOT NULL AND @Group != ''
234SET @strGroup = ' GROUP BY ' + @Group + ' '
235ELSE
236SET @strGroup = ''
237/**//*执行查询语句*/
238EXEC(
239'
240DECLARE @SortColumn ' + @type + '
241SET ROWCOUNT ' + @strStartRow + '
242Select @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' orDER BY ' + @Sort + '
243SET ROWCOUNT ' + @strPageSize + '
244Select ' + @Fields + ' FROM ' + @Tables + ' Where ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' orDER BY ' + @Sort + '
245'
246)
247GO