通用分页查询存储过程

  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