通用分页存储过程

 

  1Community_Common_Count_USP 
  2/**//* 
  3  作者: SLIGHTBOY 
  4  建立日期: 2005-8-17 
  5  修改日期: 
  6  存储作用: 通用记录统计 
  7  作用数据库: 自定义 
  8  输入变量: 
  9      @KeyColumn 主键字段名 
 10      @TableName 表格名称 
 11      @SearchQuery 查询条件 默认 '' 
 12  输出变量: 
 13  中间变量: 
 14*/ 
 15CREATE PROCEDURE dbo.Community_Common_Count_USP 
 16( 
 17  @KeyColumn varchar(20), 
 18  @TableName varchar(20), 
 19  @SearchQuery varchar(400) = '' 
 20) 
 21AS 
 22  SET NOCOUNT ON 
 23 DECLARE @Timer datetime 
 24 SET @Timer = getdate() 
 25
 26 IF ( @SearchQuery IS Not NULL ) AND ( @SearchQuery <> '' ) 
 27  exec('SELECT Count('+ @KeyColumn +') FROM ['+ @TableName +'] Where '+ @SearchQuery) 
 28 Else 
 29  exec('SELECT Count('+ @KeyColumn +') FROM ['+ @TableName +']') 
 30
 31 print(DATEDIFF(millisecond, @Timer, getdate())) 
 32GO 
 33
 34
 35Community_Common_Page_USP 
 36/**//* 
 37  作者:  SLIGHTBOY 
 38  建立日期: 2005-8-12 
 39  修改日期: 
 40  存储作用: 通用分页存储过程 
 41  作用数据库: 自定义 
 42  版本:  1.0 
 43  输入变量: 
 44      @AbsolutePage 请求页 默认 1 
 45      @PageSize  每页记录数 默认 20 
 46      @RecordCount 总记录 
 47      @KeyColumn 主键字段名 
 48      @SelectColumn 查询字段 
 49      @TableName 表格名称 
 50      @Sort  排序方式 DESC 
 51      @SearchQuery 查询条件 默认 '' 
 52      @ExpandQuery 扩展内容(FOR XML EXPLICIT) 默认 '' 
 53  输出变量: 
 54  过程变量 
 55      @SqlQuery 查询语句 
 56      @AbsolutePosition 当前位置 
 57*/ 
 58CREATE PROCEDURE dbo.Community_Common_Page_USP 
 59( 
 60  @AbsolutePage int = 1, 
 61  @PageSize int = 20, 
 62  @RecordCount int, 
 63  @KeyColumn varchar(20), 
 64  @SelectColumn varchar(1000), 
 65  @TableName varchar(20), 
 66  @Sort varchar(4) = '', 
 67  @SearchQuery varchar(400) = '', 
 68  @ExpandQuery varchar(50) = '' 
 69) 
 70AS 
 71  SET NOCOUNT ON 
 72 DECLARE @Timer datetime 
 73 SET @Timer = getdate() 
 74
 75 DECLARE @SqlQuery varchar(3000) 
 76 DECLARE @AbsolutePosition int 
 77 SET @AbsolutePosition = @AbsolutePage * @PageSize 
 78
 79 IF ( @AbsolutePosition = @PageSize ) 
 80  IF ( @Sort = 1 ) 
 81   SET @SqlQuery = ' 
 82   SELECT TOP '+ LTRIM( STR( @PageSize ) ) +' 
 83   '+ @SelectColumn +' 
 84   FROM ['+ @TableName +'] 
 85   '+ dbo.iif(@SearchQuery, 'Where '+ @SearchQuery, '' ) +' 
 86   ORDER BY '+ @KeyColumn +' ASC 
 87   '+ @ExpandQuery 
 88  Else 
 89   
 90   SET @SqlQuery = ' 
 91   SELECT TOP '+ LTRIM( STR( @PageSize ) ) +' 
 92   '+ @SelectColumn +' 
 93   FROM ['+ @TableName +'] 
 94   '+ dbo.iif(@SearchQuery, 'Where '+ @SearchQuery, '' ) +' 
 95   ORDER BY '+ @KeyColumn +' DESC 
 96   '+ @ExpandQuery 
 97 -- 大于 首页 小于 中间页 
 98 Else IF ( @AbsolutePosition <= @RecordCount/2  ) 
 99  IF ( @Sort = 1 ) 
100   SET @SqlQuery = ' 
101   SELECT TOP '+ LTRIM( STR( @PageSize ) ) +' '+ @SelectColumn +' 
102   FROM ['+ @TableName +'] 
103   WHERE '+ @KeyColumn +' > 
104   ( 
105    SELECT MAX('+ @KeyColumn +') 
106    FROM 
107    ( 
108     SELECT TOP '+ LTRIM( STR( ( @AbsolutePage - 1)*@PageSize) ) +' 
109     '+ @KeyColumn +' 
110     FROM ['+ @TableName +'] 
111     '+ dbo.iif(@SearchQuery, 'Where '+ @SearchQuery, '' ) +' 
112     ORDER BY '+ @KeyColumn +' ASC 
113    ) As Child 
114   ) 
115   '+ dbo.iif(@SearchQuery, ' AND '+ @SearchQuery, '' ) +' 
116   ORDER BY '+ @KeyColumn +' ASC 
117   '+ @ExpandQuery 
118  Else 
119   SET @SqlQuery = ' 
120   SELECT TOP '+ LTRIM( STR( @PageSize ) ) +' '+ @SelectColumn +' 
121   FROM ['+ @TableName +'] 
122   WHERE '+ @KeyColumn +' <= 
123   ( 
124    SELECT Min('+ @KeyColumn +') 
125    FROM 
126    ( 
127     SELECT TOP '+ LTRIM( STR( ( @AbsolutePage - 1)*@PageSize + 1 ) ) +' 
128     '+ @KeyColumn +' 
129     FROM ['+ @TableName +'] 
130     '+ dbo.iif(@SearchQuery, 'Where '+ @SearchQuery, '' ) +' 
131     ORDER BY '+ @KeyColumn +' DESC 
132    ) As Child 
133   ) 
134   '+ dbo.iif(@SearchQuery, ' AND '+ @SearchQuery, '' ) +' 
135   ORDER BY '+ @KeyColumn +' DESC 
136   '+ @ExpandQuery 
137 Else IF (@AbsolutePosition > @RecordCount/2 ) 
138  IF ( @Sort = 1 ) 
139   SET @SqlQuery = ' 
140   SELECT TOP '+ LTRIM( STR( @PageSize ) ) +' '+ @SelectColumn +' 
141   FROM ['+ @TableName +'] 
142   WHERE '+ @KeyColumn +' > 
143   ( 
144    SELECT Min('+ @KeyColumn +') 
145    FROM 
146    ( 
147     SELECT TOP '+ LTRIM( STR( @RecordCount - ((@AbsolutePage - 1) * @PageSize ) + 1) ) +' 
148     '+ @KeyColumn +' 
149     FROM ['+ @TableName +'] 
150     '+ dbo.iif(@SearchQuery, 'Where '+ @SearchQuery, '' ) +' 
151     ORDER BY '+ @KeyColumn +' DESC 
152    ) As Child 
153   ) 
154   '+ dbo.iif(@SearchQuery, ' AND '+ @SearchQuery, '' ) +' 
155   ORDER BY '+ @KeyColumn +' ASC 
156   '+ @ExpandQuery 
157  Else 
158   SET @SqlQuery = ' 
159   SELECT TOP '+ LTRIM( STR( @PageSize ) ) +' '+ @SelectColumn +' 
160   FROM ['+ @TableName +'] 
161   WHERE '+ @KeyColumn +' < 
162   ( 
163    SELECT MAX('+ @KeyColumn +') 
164    FROM 
165    ( 
166     SELECT TOP '+ LTRIM( STR( @RecordCount - ((@AbsolutePage - 1) * @PageSize ) + 1) ) +' 
167     '+ @KeyColumn +' 
168     FROM ['+ @TableName +'] 
169     '+ dbo.iif(@SearchQuery, 'Where '+ @SearchQuery, '' ) +' 
170     ORDER BY '+ @KeyColumn +' ASC 
171    ) As Child 
172   ) 
173   '+ dbo.iif(@SearchQuery, ' AND '+ @SearchQuery, '' ) +' 
174   ORDER BY '+ @KeyColumn +' DESC 
175   '+ @ExpandQuery 
176
177exec(@SqlQuery) 
178print(@SqlQuery) 
179print(DATEDIFF(millisecond, @Timer, getdate())) 
180
181GO 

第一个存储过程 计算总记录
第二个存储过程 为选择记录部分
根据传入页码判断位置 实现三个逻辑
请求页 = 第一页
请求页 <= 总页/2
请求页 > 总页/2
并且针对排序方式做了优化

总记录统计部分消耗资源比较大 尽可能做冗余

具体参数作用 存储过程说明里已经做了说明 这里就不重复了