千万数量级分页存储过程

  1 set ANSI_NULLS ON
  2 
  3 set QUOTED_IDENTIFIER ON
  4 go
  5 ALTER     PROCEDURE  [dbo].[PRO_PAGEDATA]
  6 /*
  7 ***************************************************************
  8 ** 千万数量级分页存储过程 **
  9 ***************************************************************
 10 参数说明:
 11 1.Tables :表名称,视图
 12 2.PrimaryKey :主关键字
 13 3.Sort :排序语句，不带Order By 比如：NewsID Desc,OrderRows Asc
 14 4.CurrentPage :当前页码
 15 5.PageSize :分页尺寸
 16 6.Filter :过滤语句，不带Where
 17 7.Group :Group语句,不带Group By
 18 8.TotalCount 返回记录总数
 19 ***************************************************************/
 20 (
 21 @Tables  varchar ( 1000 ),
 22 @PrimaryKey  varchar ( 100 ),
 23 @Sort  varchar ( 200 ) =  NULL ,
 24 @CurrentPage  int  =  1 ,
 25 @PageSize  int  =  10 ,
 26 @Fields  varchar ( 1000 ) =  '*' ,
 27 @Filter  varchar ( 1000 ) =  NULL ,
 28 @Group varchar ( 1000 ) =  NULL,
 29 @TotalCount int output
 30 )
 31 AS
 32 
 33 /*默认排序*/
 34 
 35 IF  @Sort  IS NULL OR  @Sort =  ''
 36 SET  @Sort = @PrimaryKey
 37 DECLARE  @SortTable  varchar ( 100 )
 38 DECLARE  @SortName  varchar ( 100 )
 39 DECLARE  @strSortColumn  varchar ( 200 )
 40 DECLARE  @operator  char ( 2 )
 41 DECLARE  @type  varchar ( 100 )
 42 DECLARE  @prec  int
 43 
 44 /*设定排序语句.*/
 45 
 46 IF  CHARINDEX( 'DESC' ,@Sort)> 0
 47 BEGIN
 48 SET  @strSortColumn = REPLACE(@Sort,  'DESC' ,  '' )
 49 SET  @operator =  '<='
 50 END
 51 
 52 ELSE
 53 BEGIN
 54 IF  CHARINDEX( 'ASC' , @Sort) =  0
 55 SET  @strSortColumn = REPLACE(@Sort,  'ASC' ,  '' )
 56 SET  @operator =  '>='
 57 END
 58 
 59 IF  CHARINDEX( '.' , @strSortColumn) >  0
 60 BEGIN
 61 SET  @SortTable = SUBSTRING(@strSortColumn,  0 , CHARINDEX( '.' ,@strSortColumn))
 62 SET  @SortName = SUBSTRING(@strSortColumn, CHARINDEX( '.' ,@strSortColumn) +  1 , LEN(@strSortColumn))
 63 END
 64 ELSE
 65 BEGIN
 66 SET  @SortTable = @Tables
 67 SET  @SortName = @strSortColumn
 68 END
 69 SELECT  @type=t.name, @prec=c.prec
 70 FROM  sysobjects o
 71 JOIN  syscolumns c  on  o.id=c.id
 72 JOIN  systypes t  on  c.xusertype=t.xusertype
 73 WHERE  o.name = @SortTable  AND  c.name = @SortName
 74 IF  CHARINDEX( 'char' , @type) >  0
 75 SET  @type = @type +  '('  +  CAST (@prec  AS varchar ) +  ')'
 76 DECLARE  @strPageSize  varchar ( 50 )
 77 DECLARE  @strStartRow  varchar ( 50 )
 78 DECLARE  @strFilter  varchar ( 1000 )
 79 DECLARE  @strSimpleFilter  varchar ( 1000 )
 80 DECLARE  @strGroup  varchar ( 1000 )
 81 
 82 /*默认当前页*/
 83 IF  @CurrentPage <  1
 84 SET  @CurrentPage =  1
 85 /*设置分页参数.*/
 86 SET  @strPageSize =  CAST (@PageSize  AS varchar ( 50 ))
 87 SET  @strStartRow =  CAST (((@CurrentPage -  1 )*@PageSize +  1 )  AS varchar ( 50 ))
 88 
 89 /*筛选以及分组语句.*/
 90 declare @strSQL nvarchar(4000)
 91 
 92 IF  @Filter  IS NOT NULL AND  @Filter !=  ''
 93 BEGIN
 94 SET  @strFilter =  ' WHERE '  + @Filter +  ' '
 95 SET  @strSimpleFilter =  ' AND '  + @Filter +  ' '
 96 set @strSQL = ' select @TotalCount=count(*) from '+@Tables+' where '+@Filter+''
 97 END
 98 
 99 ELSE
100 BEGIN
101 
102 SET  @strSimpleFilter =  ''
103 SET  @strFilter =  ''
104 set @strSQL = ' select @TotalCount=count(*) from '+@Tables+''
105 END
106 IF  @Group IS NOT NULL AND  @Group  !=  ''
107 SET  @strGroup =  ' GROUP BY '  + @Group  +  ' '
108 ELSE
109 SET  @strGroup =  ''
110 
111 --统计总条数 
112 
113 /*声明查询字符串*/
114 
115 /*
116 
117 set @strSQL='
118 
119 DECLARE @SortColumn '  + @type +  '
120 
121 SET ROWCOUNT '  + @strStartRow +  '
122 
123 SELECT @SortColumn='  + @strSortColumn +  ' FROM '  + @Tables + @strFilter +  ' '  + @strGroup +  ' ORDER BY '  + @Sort +  '
124 
125 SET ROWCOUNT '  + @strPageSize +  '
126 
127 SELECT '  + @Fields +  'FROM '  + @Tables +  ' WHERE '  + @strSortColumn + @operator +  ' @SortColumn '  + @strSimpleFilter +  ' '  + @strGroup +  ' ORDER BY '  + @Sort +  '
128 
129 '
130 
131 */
132 
133 /*取得查询结果总数*/
134  exec sp_executesql
135  @strSQL, 
136  N'@TotalCount int=0 OUTPUT', 
137  @TotalCount=@TotalCount OUTPUT 
138 /*执行查询语句*/
139 
140 EXEC (
141 '
142 DECLARE @SortColumn '  + @type +  '
143 SET ROWCOUNT '  + @strStartRow +  '
144 SELECT @SortColumn='  + @strSortColumn +  ' FROM '  + @Tables + @strFilter +  ' '  + @strGroup +  ' ORDER BY '  + @Sort +  '
145 SET ROWCOUNT '  + @strPageSize +  '
146 SELECT '  + @Fields +  'FROM '  + @Tables +  ' WHERE '  + @strSortColumn + @operator +  ' @SortColumn '  + @strSimpleFilter +  ' '  + @strGroup +  ' ORDER BY '  + @Sort +  '
147 '
148 )