私奔
加油中的私奔```~~

前段时间由于项目需求想上网搜个通用分页存储过程先用着
本以为非常简单的事竟然让我大失所望
竟然没有一个能够满足我的需求,不是效率太低就是限制太多不符合实际需要
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)由于时间关系,只写了一个简单示例,需要的朋友可以下载
            分页示例

 

  1if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Paging_RowCount]'and OBJECTPROPERTY(id, N'IsProcedure'= 1)
  2drop procedure [dbo].[Paging_RowCount]
  3GO
  4
  5SET QUOTED_IDENTIFIER ON 
  6GO
  7SET ANSI_NULLS ON 
  8GO
  9---------------------------------------------------------------
 10-- 分页存储过程(使用RowCount)  --edit by SiBen
 11-- summary:
 12--          获取表或表集合的分页数据
 13--          当多表连接时,sort列必须指定表名        
 14---------------------------------------------------------------
 15
 16CREATE 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)
 28AS
 29
 30/*Default Sorting*/
 31IF @Sort IS NULL OR @Sort = ''
 32    SET @Sort = @PK
 33
 34/*Find the @PK type*/
 35DECLARE @SortTable varchar(100)
 36DECLARE @SortName varchar(100)
 37DECLARE @PKTable varchar(100)
 38DECLARE @PKName varchar(100)
 39DECLARE @strSortColumn varchar(200)
 40DECLARE @operator char(2)
 41DECLARE @type varchar(100)
 42DECLARE @prec int
 43
 44/*Set sorting variables.*/    
 45IF CHARINDEX('DESC',@Sort)>0
 46    BEGIN
 47        SET @strSortColumn = REPLACE(@Sort'DESC''')
 48        SET @operator = '<'
 49    END
 50ELSE
 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 */
 61IF CHARINDEX('.'@strSortColumn> 0
 62    BEGIN
 63        SET @SortTable = SUBSTRING(@strSortColumn0CHARINDEX('.',@strSortColumn))
 64        SET @SortName = SUBSTRING(@strSortColumnCHARINDEX('.',@strSortColumn+ 1LEN(@strSortColumn))
 65    END
 66ELSE
 67    BEGIN
 68        SET @SortTable = @Tables
 69        SET @SortName = @strSortColumn
 70    END
 71IF CHARINDEX('.'@PK> 0
 72    BEGIN
 73        SET @PKTable = SUBSTRING(@PK0CHARINDEX('.',@PK))
 74        SET @PKName = SUBSTRING(@PKCHARINDEX('.',@PK+ 1LEN(@PK))
 75    END
 76ELSE
 77    BEGIN
 78        SET @PKTable = @Tables
 79        SET @PKName = @PK
 80    END
 81
 82SELECT @type=t.name, @prec=c.prec
 83FROM sysobjects o 
 84JOIN syscolumns c on o.id=c.id
 85JOIN systypes t on c.xusertype=t.xusertype
 86WHERE o.name = @SortTable AND c.name = @SortName
 87
 88IF CHARINDEX('char'@type> 0
 89   SET @type = @type + '(' + CAST(@prec AS varchar+ ')'
 90
 91DECLARE @strPageSize varchar(50)
 92DECLARE @strStartRow varchar(50)
 93DECLARE @strFilter varchar(1000)
 94DECLARE @strSimpleFilter varchar(1000)
 95DECLARE @strGroup varchar(1000)
 96
 97/*Default Page Number*/
 98IF @PageNumber < 1
 99    SET @PageNumber = 1
100
101/*Set paging variables.*/
102SET @strPageSize = CAST(@PageSize AS varchar(50))
103SET @strStartRow = CAST(((@PageNumber - 1)*@PageSize + 1AS varchar(50))
104
105/*Set filter & group variables.*/
106IF @Filter IS NOT NULL AND @Filter != ''
107    BEGIN
108        SET @strFilter = ' WHERE ' + @Filter + ' '
109        SET @strSimpleFilter = ' AND ' + @Filter + ' '
110    END
111ELSE
112    BEGIN
113        SET @strSimpleFilter = ''
114        SET @strFilter = ''
115    END
116IF @Group IS NOT NULL AND @Group != ''
117    SET @strGroup = ' GROUP BY ' + @Group + ' '
118ELSE
119    SET @strGroup = ''
120
121/*Get rows count.*/
122DECLARE @str_Count_SQL nvarchar(500)
123SET @str_Count_SQL= 'SELECT @TotalCount=count(*) FROM ' + @Tables + @strFilter
124EXEC sp_executesql @str_Count_SQL,N'@TotalCount int=0 output',@RecordCount output
125    
126/*Execute dynamic query*/    
127IF @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
139ELSE
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
168GO
169SET QUOTED_IDENTIFIER OFF 
170GO
171SET ANSI_NULLS ON 
172GO
173
174
posted on 2006-01-11 01:54  私奔  阅读(2684)  评论(15编辑  收藏  举报