分页存储过程

  1 USE [database]
  2 GO
  3 /****** Object:  StoredProcedure [dbo].[SP_Com_SelectByPage]    Script Date: 03/03/2014 13:01:19 ******/
  4 SET ANSI_NULLS ON
  5 GO
  6 SET QUOTED_IDENTIFIER ON
  7 GO
  8 ALTER PROCEDURE [dbo].[SP_Com_SelectByPage]
  9 (
 10 @tblName     nvarchar(Max),        ----要显示的表或多个表的连接
 11 @fldName     nvarchar(max) = ‘*‘,    ----要显示的字段列表
 12 @pageSize    int = 1,        ----每页显示的记录个数
 13 @page        int = 1,        ----要显示那一页的记录
 14 @fldSort    nvarchar(max) = null,    ----排序字段列表或条件
 15 @Sort        bit = 1,        ----排序方法，0为升序，1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如：‘ SortA Asc,SortB Desc,SortC ‘)
 16 @strCondition    nvarchar(max) = null,    ----查询条件,不需where
 17 @ID        nvarchar(150),        ----主表的主键
 18 @Dist                 bit = 0           ----是否添加查询字段的 DISTINCT 默认0不添加/1添加
 19 )
 20 AS
 21 SET NOCOUNT ON
 22 Declare @sqlTmp nvarchar(max)        ----存放动态生成的SQL语句
 23 Declare @strTmp nvarchar(max)        ----存放取得查询结果总数的查询语句
 24 Declare @strID     nvarchar(max)        ----存放取得查询开头或结尾ID的查询语句
 25 Declare @pageCount    int             ----查询结果分页后的总页数
 26 Declare @Counts    int                 ----查询到的记录数
 27 Declare @strSortType nvarchar(10)    ----数据排序规则A
 28 Declare @strFSortType nvarchar(10)    ----数据排序规则B
 29 
 30 Declare @SqlSelect nvarchar(max)         ----对含有DISTINCT的查询进行SQL构造
 31 Declare @SqlSelectCount nvarchar(max)
 32 Declare @SqlCounts nvarchar(max)          ----对含有DISTINCT的总数查询进行SQL构造
 33 Declare @FSort     nvarchar(max)
 34 Declare @DSort     nvarchar(max)
 35 set @pageCount=1
 36 set @Counts=1
 37 
 38 if @Dist  = 0
 39 begin
 40     set @SqlSelect = ‘select ‘
 41     set @SqlSelectCount = ‘select ‘
 42     set @SqlCounts = ‘Count(*)‘
 43 end
 44 else
 45 begin
 46     set @SqlSelect = ‘select distinct ‘
 47     --set @SqlCounts = ‘Count(DISTINCT ‘+@ID+‘)‘
 48     set @SqlSelectCount = ‘count(*) from ( ‘
 49     set @SqlCounts = ‘select distinct ‘+@ID+‘ from ‘+@tblName+‘)as T‘
 50 end
 51 
 52 
 53 if @Sort=0
 54 begin
 55     set @strFSortType=‘ ASC ‘
 56     set @strSortType=‘ DESC ‘
 57 end
 58 else
 59 begin
 60     set @strFSortType=‘ DESC ‘
 61     set @strSortType=‘ ASC ‘
 62 end
 63 
 64 if @fldSort IS NOT NULL or @fldSort <>‘‘  
 65 begin
 66     set @FSort=‘ order by ‘+ @fldSort +‘ ‘+ @strFSortType 
 67     set @DSort=‘ order by ‘+ @fldSort +‘ ‘+ @strSortType 
 68 end
 69 ELSE
 70 Begin
 71    SET @fldSort=‘‘
 72 END
 73 
 74 
 75 
 76 
 77 --------生成查询语句--------
 78 --此处@strTmp为取得查询结果数量的语句
 79 --print @strCondition
 80 if @strCondition is null or @strCondition=‘‘     --没有设置显示条件
 81 begin   
 82 if @Dist  = 0
 83     begin
 84     set @sqlTmp =  @fldName + ‘ From ‘ + @tblName
 85     set @strTmp = @SqlSelect+‘ @Counts=‘+@SqlCounts+‘ FROM ‘+@tblName
 86     set @strID = ‘ From ‘ + @tblName
 87     end
 88 else
 89     begin
 90     set @sqlTmp =  @fldName + ‘ From ‘ + @tblName
 91     set @strTmp = @SqlSelect+‘ @Counts=‘+@SqlSelectCount+@SqlCounts
 92     set @strID = ‘ From ‘ + @tblName
 93     end 
 94 end
 95 else
 96 begin
 97 if @Dist =0
 98     begin
 99     set @sqlTmp = + @fldName + ‘From ‘ + @tblName + ‘ where (1>0) ‘ + @strCondition
100     set @strTmp = @SqlSelect+‘ @Counts=‘+@SqlCounts+‘ FROM ‘+@tblName + ‘ where (1>0) ‘ + @strCondition
101     set @strID = ‘ From ‘ + @tblName + ‘ where (1>0) ‘ + @strCondition
102     end
103 else
104    begin
105     set @sqlTmp = + @fldName + ‘From ‘ + @tblName + ‘ where (1>0) ‘ + @strCondition
106     set @strTmp = @SqlSelect+‘ @Counts=‘+@SqlSelectCount+‘select distinct ‘+@ID+‘ from ‘+@tblName+‘ where (1>0) ‘ + @strCondition+‘)as T‘
107     set @strID = ‘ From ‘ + @tblName + ‘ where (1>0) ‘ + @strCondition
108    end
109 end
110 --print @strTmp
111 ----取得查询结果总数量-----
112 exec sp_executesql @strTmp,N‘@Counts int out ‘,@Counts out
113 declare @tmpCounts int
114 if @Counts = 0
115     set @tmpCounts = 1
116 else
117     set @tmpCounts = @Counts
118 
119     --取得分页总数
120     set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize
121 
122     /**//**当前页大于总页数 取最后一页**/
123     if @page>@pageCount
124         set @page=@pageCount
125 
126     --/*-----数据分页2分处理-------*/
127     declare @pageIndex int --总数/页大小
128     declare @lastcount int --总数%页大小 
129 
130     set @pageIndex = @tmpCounts/@pageSize
131     set @lastcount = @tmpCounts%@pageSize
132     if @lastcount > 0
133         set @pageIndex = @pageIndex + 1
134     else
135         set @lastcount = @pageSize
136 
137     --//***显示分页
138     if @strCondition is null or @strCondition=‘‘     --没有设置显示条件
139     begin
140         --if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2   --前半部分数据处理
141             --begin 
142                 set @strTmp=  @SqlSelect+‘ * from (‘+@SqlSelect+‘ ‘+@fldName+‘,Row_number() over(‘+ @FSort+‘) as IDRank from ‘+@tblName+‘)‘
143    +‘ AS IDWithRowNumber where IDRank>‘+CAST(@pageSize*(@page-1) AS Varchar(20)) +‘ and IDRank<‘+CAST(@pageSize*@page+1 AS Varchar(20))
144             --end
145         --else
146         --    begin
147         --    set @page = @pageIndex-@page+1 --后半部分数据处理
148         --        if @page <= 1 --最后一页数据显示
149         --            set @strTmp=@SqlSelect+‘ * from (‘+@SqlSelect+‘ top ‘+ CAST(@lastcount as VARCHAR(4))+‘ ‘+ @fldName+‘ from ‘+@tblName
150         --                +@DSort+‘) AS TempTB‘+@FSort
151         --        else                
152         --            set @strTmp=@SqlSelect+‘ * from (‘+@SqlSelect+‘ top ‘+ CAST(@pageSize as VARCHAR(4))+‘ ‘+ @fldName+‘ from ‘+@tblName
153         --                +‘ where ‘+@ID+‘ not in(‘+ @SqlSelect+‘ top ‘+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +‘ ‘+ @ID +‘ from ‘+@tblName
154         --                +@DSort+‘)‘
155         --                +@DSort+‘) AS TempTB‘+@FSort
156         --    end
157     end
158 
159     else --有查询条件
160     begin
161         --if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2   --前半部分数据处理
162         --begin 
163                 set @strTmp=  @SqlSelect+‘ * from (‘+@SqlSelect+‘ ‘+@fldName+‘,Row_number() over(‘+ @FSort+‘) as IDRank from ‘+@tblName+‘ Where (1>0) ‘+@strCondition+‘)‘
164    +‘ AS IDWithRowNumber where IDRank>‘+CAST(@pageSize*(@page-1) as Varchar(20))+‘ and IDRank<‘+CAST(@pageSize*@page+1 as Varchar(20))                 
165         --end
166         --else
167         --begin 
168         --    set @page = @pageIndex-@page+1 --后半部分数据处理
169         --    if @page <= 1 --最后一页数据显示
170         --            set @strTmp=@SqlSelect+‘ * from (‘+@SqlSelect+‘ top ‘+ CAST(@lastcount as VARCHAR(4))+‘ ‘+ @fldName+‘ from ‘+@tblName
171         --                +‘ where (1>0) ‘+ @strCondition +@DSort+‘) AS TempTB‘+@FSort
172         --    else
173         --            set @strTmp=@SqlSelect+‘ * from (‘+@SqlSelect+‘ top ‘+ CAST(@pageSize as VARCHAR(4))+‘ ‘+ @fldName+‘ from ‘+@tblName
174         --                +‘ where ‘+@ID+‘ not in(‘+ @SqlSelect+‘ top ‘+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +‘ ‘+ @ID +‘ from ‘+@tblName
175         --                +‘ where (1>0) ‘+ @strCondition +@DSort+‘)‘
176         --                + @strCondition +@DSort+‘) AS TempTB‘+@FSort 
177         --end    
178     end
179     --print @strTmp
180     ------返回查询结果-----
181 
182 exec sp_executesql @strTmp
183 SELECT @pageCount AS PageCount,@Counts AS Counts
184 --print @strTmp
185 SET NOCOUNT OFF