通用分页查询存储过程

 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