优化后的通用分页存储过程(已更新)

前段时间由于项目需求想上网搜个通用分页存储过程先用着
本以为非常简单的事竟然让我大失所望
竟然没有一个能够满足我的需求,不是效率太低就是限制太多不符合实际需要
bug也是一顿狂冒,更有甚者执行都执行不了
我想应该很多朋友都有这样的遭遇吧
于是一气之下决定自己研究

在众多分析性能的文章中,很庆幸翻到了这一篇http://www.codeproject.com/aspnet/PagingLarge.asp(感谢园子里一位朋友的提示,具体名字已经不记得了),对分页存储过程有兴趣的朋友可以去看,写的很详尽,随便PF一下国外同行的认真和专业.

在上面提到的那篇文章的一开始大力推崇使用RowCoun的t方法,
可是由于原文中提供的方法不支持非unique字段的排序
大多场合都并不适用
文章末尾连作者自己提到,已经开始考虑改用cursor方法
可我对RowCount方法算是情有独中,于是对原文中该方法进行了改进
改进后的方法已基本上满足我的需要,现在发布出来,有用的朋友可以下载试用

修改记录:
    1)增加对非unique字段排序的支持,但必须设定一个PK字段(注:只要是unique字段都可以作为pk字段)
    2)增加记录总数输出参数
    3)修改原过程若干BUG
    4)修改PK字段只能是int型的bug(刚发现的,^_^)

注:
     1)如表名参数为多表连接时,sort列必须指定表名;
     2)只支持单字段排序,有朋友如果问为什么不做成可以多字段排序的,理论上确实有这种可能性,但需要以一定的效率损失为代价,而且会使方法过于复杂,如真有这种需要,完全可以写一个单独的分页存储过程,无论在性能还是复杂度上都比通用要简单.
     3)由于时间原因没有大量测试,如有BUG,请您提出,立刻修正;
     4)由于时间关系,只写了一个简单示例,需要的朋友可以下载
            分页示例

 

  1 if   exists  ( select   *   from  dbo.sysobjects  where  id  =   object_id (N ' [dbo].[Paging_RowCount] ' )  and   OBJECTPROPERTY (id, N ' IsProcedure ' )  =   1 )
  2 drop   procedure   [ dbo ] . [ Paging_RowCount ]
  3 GO
  4
  5 SET  QUOTED_IDENTIFIER  ON  
  6 GO
  7 SET  ANSI_NULLS  ON  
  8 GO
  9 -- -------------------------------------------------------------
 10 --  分页存储过程(使用RowCount)  --edit by SiBen
 11 --  summary:
 12 --           获取表或表集合的分页数据
 13 --           当多表连接时,sort列必须指定表名        
 14 -- -------------------------------------------------------------
 15
 16 CREATE   PROCEDURE  Paging_RowCount
 17 (
 18 @Tables   varchar ( 1000 ),
 19 @PK   varchar ( 100 ),
 20 @Sort   varchar ( 200 )  =   NULL ,
 21 @PageNumber   int   =   1 ,
 22 @PageSize   int   =   10 ,
 23 @Fields   varchar ( 1000 )  =   ' * ' ,
 24 @Filter   varchar ( 1000 )  =   NULL ,
 25 @Group   varchar ( 1000 )  =   NULL ,
 26 @RecordCount   int   =   0  output 
 27 )
 28 AS
 29
 30 /**/ /*Default Sorting*/
 31 IF   @Sort   IS   NULL   OR   @Sort   =   ''
 32      SET   @Sort   =   @PK
 33
 34 /**/ /*Find the @PK type*/
 35 DECLARE   @SortTable   varchar ( 100 )
 36 DECLARE   @SortName   varchar ( 100 )
 37 DECLARE   @PKTable   varchar ( 100 )
 38 DECLARE   @PKName   varchar ( 100 )
 39 DECLARE   @strSortColumn   varchar ( 200 )
 40 DECLARE   @operator   char ( 2 )
 41 DECLARE   @type   varchar ( 100 )
 42 DECLARE   @prec   int
 43
 44 /**/ /*Set sorting variables.*/     
 45 IF   CHARINDEX ( ' DESC ' , @Sort ) > 0
 46      BEGIN
 47          SET   @strSortColumn   =   REPLACE ( @Sort ,  ' DESC ' ,  '' )
 48          SET   @operator   =   ' < '
 49      END
 50 ELSE
 51      BEGIN
 52          IF   CHARINDEX ( ' ASC ' ,  @Sort )  >   0
 53              SET   @strSortColumn   =   REPLACE ( @Sort ,  ' ASC ' ,  '' )
 54                  ELSE
 55                          SET   @strSortColumn   =   @Sort
 56
 57          SET   @operator   =   ' > '
 58      END
 59
 60 /**/ /* Set PK,Sort name */
 61 IF   CHARINDEX ( ' . ' ,  @strSortColumn )  >   0
 62      BEGIN
 63          SET   @SortTable   =   SUBSTRING ( @strSortColumn ,  0 ,  CHARINDEX ( ' . ' , @strSortColumn ))
 64          SET   @SortName   =   SUBSTRING ( @strSortColumn ,  CHARINDEX ( ' . ' , @strSortColumn )  +   1 ,  LEN ( @strSortColumn ))
 65      END
 66 ELSE
 67      BEGIN
 68          SET   @SortTable   =   @Tables
 69          SET   @SortName   =   @strSortColumn
 70      END
 71 IF   CHARINDEX ( ' . ' ,  @PK )  >   0
 72      BEGIN
 73          SET   @PKTable   =   SUBSTRING ( @PK ,  0 ,  CHARINDEX ( ' . ' , @PK ))
 74          SET   @PKName   =   SUBSTRING ( @PK ,  CHARINDEX ( ' . ' , @PK )  +   1 ,  LEN ( @PK ))
 75      END
 76 ELSE
 77      BEGIN
 78          SET   @PKTable   =   @Tables
 79          SET   @PKName   =   @PK
 80      END
 81
 82 SELECT   @type = t.name,  @prec = c.prec
 83 FROM  sysobjects o 
 84 JOIN  syscolumns c  on  o.id = c.id
 85 JOIN  systypes t  on  c.xusertype = t.xusertype
 86 WHERE  o.name  =   @SortTable   AND  c.name  =   @SortName
 87
 88 IF   CHARINDEX ( ' char ' ,  @type )  >   0
 89     SET   @type   =   @type   +   ' ( '   +   CAST ( @prec   AS   varchar )  +   ' ) '
 90
 91 DECLARE   @strPageSize   varchar ( 50 )
 92 DECLARE   @strStartRow   varchar ( 50 )
 93 DECLARE   @strFilter   varchar ( 1000 )
 94 DECLARE   @strSimpleFilter   varchar ( 1000 )
 95 DECLARE   @strGroup   varchar ( 1000 )
 96
 97 /**/ /*Default Page Number*/
 98 IF   @PageNumber   <   1
 99      SET   @PageNumber   =   1
100
101 /**/ /*Set paging variables.*/
102 SET   @strPageSize   =   CAST ( @PageSize   AS   varchar ( 50 ))
103 SET   @strStartRow   =   CAST ((( @PageNumber   -   1 ) * @PageSize   +   1 )  AS   varchar ( 50 ))
104
105 /**/ /*Set filter & group variables.*/
106 IF   @Filter   IS   NOT   NULL   AND   @Filter   !=   ''
107      BEGIN
108          SET   @strFilter   =   '  WHERE  '   +   @Filter   +   '   '
109          SET   @strSimpleFilter   =   '  AND  '   +   @Filter   +   '   '
110      END
111 ELSE
112      BEGIN
113          SET   @strSimpleFilter   =   ''
114          SET   @strFilter   =   ''
115      END
116 IF   @Group   IS   NOT   NULL   AND   @Group   !=   ''
117      SET   @strGroup   =   '  GROUP BY  '   +   @Group   +   '   '
118 ELSE
119      SET   @strGroup   =   ''
120
121 /**/ /*Get rows count.*/
122 DECLARE   @str_Count_SQL   nvarchar ( 500 )
123 SET   @str_Count_SQL =   ' SELECT @TotalCount=count(*) FROM  '   +   @Tables   +   @strFilter
124 EXEC  sp_executesql  @str_Count_SQL ,N ' @TotalCount int=0 output ' , @RecordCount  output
125     
126 /**/ /*Execute dynamic query*/     
127 IF   @PKTable   =   @SortTable   and   @PKName   =   @SortName
128      BEGIN   
129          EXEC (
130          '
131         DECLARE @SortColumn  '   +   @type   +   '
132         SET ROWCOUNT  '   +   @strStartRow   +   '
133         SELECT @SortColumn= '   +   @strSortColumn   +   '  FROM  '   +   @Tables   +   @strFilter   +   '   '   +   @strGroup   +   '  ORDER BY  '   +   @Sort   +   '
134         SET ROWCOUNT  '   +   @strPageSize   +   '
135         SELECT  '   +   @Fields   +   '  FROM  '   +   @Tables   +   '  WHERE  '   +   @strSortColumn   +   @operator   +   ' = @SortColumn  '   +   @strSimpleFilter   +   '   '   +   @strGroup   +   '  ORDER BY  '   +   @Sort   +   '
136          '
137         )
138      END
139 ELSE
140      BEGIN
141                  /**/ /* Get PK Type */
142          DECLARE   @pktype   varchar ( 100 )
143                  DECLARE   @pkprec   int
144         
145          SELECT   @pktype = t.name,  @pkprec = c.prec
146          FROM  sysobjects o 
147          JOIN  syscolumns c  on  o.id = c.id
148          JOIN  systypes t  on  c.xusertype = t.xusertype
149          WHERE  o.name  =   @PKTable   AND  c.name  =   @PKName
150
151          IF   CHARINDEX ( ' char ' ,  @pktype )  >   0
152             SET   @pktype   =   @pktype   +   ' ( '   +   CAST ( @pkprec   AS   varchar )  +   ' ) '
153
154                  /**/ /*Execute dynamic query*/     
155          EXEC (
156          '
157         DECLARE @SortColumn  '   +   @type   +   '
158         DECLARE @SortNullValue  '   +   @type   +   '
159         DECLARE @PKStartValue  '   +   @pktype   +   '
160         SET @SortNullValue=CAST( ''''  as  ' +   @type   + ' )
161         SET ROWCOUNT  '   +   @strStartRow   +   '
162         SELECT @SortColumn= isNull( '   +   @strSortColumn   +   ' ,@SortNullValue), @PKStartValue =  ' +   @PK   + '  FROM  '   +   @Tables   +   @strFilter   +   '   '   +   @strGroup   +   '  ORDER BY  '   +   @Sort   +   ' , ' +   @PK   + '  Desc 
163         SET ROWCOUNT  '   +   @strPageSize   +   '
164         SELECT  '   +   @Fields   +   '  FROM  '   +   @Tables   +   '  WHERE (isNull( '   +   @strSortColumn + ' ,@SortNullValue) '   +   @operator   +   '  @SortColumn or (isNull( '   +   @strSortColumn + ' ,@SortNullValue)=@SortColumn and  ' +   @PK   + ' <=@PKStartValue)) '   +   @strSimpleFilter   +   '   '   +   @strGroup   +   '  ORDER BY  '   +   @Sort   +   ' , ' +   @PK   + '  Desc
165          '
166         )
167      END
168 GO
169 SET  QUOTED_IDENTIFIER  OFF  
170 GO
171 SET  ANSI_NULLS  ON  
172 GO
173
174

转载于:https://www.cnblogs.com/siben168/archive/2006/01/11/314620.html