1 USE [XXXXXXXX.BbbbbbbbCcccccccc.Dataware] 2 GO 3 /****** Object: StoredProcedure [dbo].[usp_CommonDataResourcePaged] Script Date: 2018/4/20 9:01:14 ******/ 4 SET ANSI_NULLS ON 5 GO 6 SET QUOTED_IDENTIFIER ON 7 GO 8 -- ============================================= 9 -- Author: <PatrickLiu> 10 -- Create date: <2018-4-16 14:21> 11 -- Description: <通用的数据分页存储过程> 12 -- ============================================= 13 ALTER PROCEDURE [dbo].[usp_CommonDataResourcePaged] 14 ( 15 @TableName nvarchar(200), ----要显示的表或多个表的连接 16 @FieldList nvarchar(1500) = '*', ----要显示的字段列表 17 @PageSize int = 20, ----每页显示的记录个数 18 @PageNumber int = 1, ----要显示那一页的记录 19 @SortFields nvarchar(1000) = null, ----排序字段列表或条件 20 @EnabledSort bit = 0, ----排序方法，0为升序，1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如：' SortA Asc,SortB Desc,SortC ') 21 @QueryCondition nvarchar(1500) = null, ----查询条件,不需WHERE 22 @Primarykey nvarchar(50), ----主表的主键 23 @EnabledDistinct bit = 0, ----是否添加查询字段的 DISTINCT 默认0不添加/1添加 24 @PageCount int = 1 output, ----查询结果分页后的总页数 25 @RecordCount int = 1 output ----查询到的记录数 26 ) 27 AS 28 SET NOCOUNT ON 29 Declare @SqlResult nvarchar(1000) ----存放动态生成的SQL语句 30 Declare @SqlTotalCount nvarchar(1000) ----存放取得查询结果总数的查询语句 31 Declare @SqlStartOrEndID nvarchar(1000) ----存放取得查询开头或结尾ID的查询语句 32 33 Declare @SortTypeA nvarchar(10) ----数据排序规则A 34 Declare @SortTypeB nvarchar(10) ----数据排序规则B 35 36 Declare @SqlDistinct nvarchar(50) ----对含有DISTINCT的查询进行SQL构造 37 Declare @SqlCountDistinct nvarchar(50) ----对含有DISTINCT的总数查询进行SQL构造 38 39 declare @timediff datetime --耗时测试时间差 40 SELECT @timediff=getdate() 41 42 if @EnabledDistinct = 0 43 begin 44 set @SqlDistinct = 'SELECT ' 45 set @SqlCountDistinct = 'Count(*)' 46 end 47 else 48 begin 49 set @SqlDistinct = 'SELECT DISTINCT ' 50 set @SqlCountDistinct = 'Count(DISTINCT '+@Primarykey+')' 51 end 52 53 if @EnabledSort=0 54 begin 55 set @SortTypeB=' ASC ' 56 set @SortTypeA=' DESC ' 57 end 58 else 59 begin 60 set @SortTypeB=' DESC ' 61 set @SortTypeA=' ASC ' 62 end 63 64 --------生成查询语句-------- 65 --此处@SqlTotalCount为取得查询结果数量的语句 66 if @QueryCondition is null or @QueryCondition='' --没有设置显示条件 67 begin 68 set @SqlResult = @FieldList + ' From ' + @TableName 69 set @SqlTotalCount = @SqlDistinct+' @RecordCount='+@SqlCountDistinct+' FROM '+@TableName 70 set @SqlStartOrEndID = ' From ' + @TableName 71 end 72 else 73 begin 74 set @SqlResult = + @FieldList + ' From ' + @TableName + ' WHERE (1>0) and ' + @QueryCondition 75 set @SqlTotalCount = @SqlDistinct+' @RecordCount='+@SqlCountDistinct+' FROM '+@TableName + ' WHERE (1>0) and ' + @QueryCondition 76 set @SqlStartOrEndID = ' From ' + @TableName + ' WHERE (1>0) and ' + @QueryCondition 77 end 78 79 ----取得查询结果总数量----- 80 exec sp_executesql @SqlTotalCount,N'@RecordCount int out ',@RecordCount out 81 82 declare @TemporaryCount int --临时统计 83 if @RecordCount = 0 84 set @TemporaryCount = 1 85 else 86 set @TemporaryCount = @RecordCount 87 88 --取得分页总数 89 set @PageCount=(@TemporaryCount+@PageSize-1)/@PageSize 90 91 /**当前页大于总页数 取最后一页**/ 92 if @PageNumber>@PageCount 93 set @PageNumber=@PageCount 94 95 --/*-----数据分页2分处理-------*/ 96 declare @pageIndex int --总数/页大小 97 declare @lastcount int --总数%页大小 98 99 set @pageIndex = @TemporaryCount/@PageSize 100 set @lastcount = @TemporaryCount%@PageSize 101 if @lastcount > 0 102 set @pageIndex = @pageIndex + 1 103 else 104 set @lastcount = @pagesize 105 106 --//***显示分页 107 if @QueryCondition is null or @QueryCondition='' --没有设置显示条件 108 begin 109 if @pageIndex<2 or @PageNumber<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理 110 begin 111 if @PageNumber=1 112 set @SqlTotalCount=@SqlDistinct+' TOP '+ CAST(@PageSize as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName+' ORDER BY '+ @SortFields +' '+ @SortTypeB 113 else 114 begin 115 if @EnabledSort=1 116 begin 117 set @SqlTotalCount=@SqlDistinct+' TOP '+ CAST(@PageSize as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName 118 +' WHERE '+@Primarykey+' <(SELECT MIN('+ @Primarykey +') FROM ('+ @SqlDistinct+' TOP '+ CAST(@PageSize*(@PageNumber-1) as Varchar(20)) +' '+ @Primarykey +' FROM '+@TableName 119 +' ORDER BY '+ @SortFields +' '+ @SortTypeB+') AS TBMinID)'+' ORDER BY '+ @SortFields +' '+ @SortTypeB 120 end 121 else 122 begin 123 set @SqlTotalCount=@SqlDistinct+' TOP '+ CAST(@PageSize as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName 124 +' WHERE '+@Primarykey+' >(SELECT MAX('+ @Primarykey +') FROM ('+ @SqlDistinct+' TOP '+ CAST(@PageSize*(@PageNumber-1) as Varchar(20)) +' '+ @Primarykey +' FROM '+@TableName 125 +' ORDER BY '+ @SortFields +' '+ @SortTypeB+') AS TBMinID)'+' ORDER BY '+ @SortFields +' '+ @SortTypeB 126 end 127 end 128 end 129 else 130 begin 131 set @PageNumber = @pageIndex-@PageNumber+1 --后半部分数据处理 132 if @PageNumber <= 1 --最后一页数据显示 133 set @SqlTotalCount=@SqlDistinct+' * FROM ('+@SqlDistinct+' TOP '+ CAST(@lastcount as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName 134 +' ORDER BY '+ @SortFields +' '+ @SortTypeA+') AS TempTB'+' ORDER BY '+ @SortFields +' '+ @SortTypeB 135 else 136 if @EnabledSort=1 137 begin 138 set @SqlTotalCount=@SqlDistinct+' * FROM ('+@SqlDistinct+' TOP '+ CAST(@PageSize as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName 139 +' WHERE '+@Primarykey+' >(SELECT MAX('+ @Primarykey +') FROM('+ @SqlDistinct+' TOP '+ CAST(@PageSize*(@PageNumber-2)+@lastcount as Varchar(20)) +' '+ @Primarykey +' FROM '+@TableName 140 +' ORDER BY '+ @SortFields +' '+ @SortTypeA+') AS TBMaxID)' 141 +' ORDER BY '+ @SortFields +' '+ @SortTypeA+') AS TempTB'+' ORDER BY '+ @SortFields +' '+ @SortTypeB 142 end 143 else 144 begin 145 set @SqlTotalCount=@SqlDistinct+' * FROM ('+@SqlDistinct+' TOP '+ CAST(@PageSize as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName 146 +' WHERE '+@Primarykey+' <(SELECT MIN('+ @Primarykey +') FROM('+ @SqlDistinct+' TOP '+ CAST(@PageSize*(@PageNumber-2)+@lastcount as Varchar(20)) +' '+ @Primarykey +' FROM '+@TableName 147 +' ORDER BY '+ @SortFields +' '+ @SortTypeA+') AS TBMaxID)' 148 +' ORDER BY '+ @SortFields +' '+ @SortTypeA+') AS TempTB'+' ORDER BY '+ @SortFields +' '+ @SortTypeB 149 end 150 end 151 end 152 153 else --有查询条件 154 begin 155 if @pageIndex<2 or @PageNumber<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理 156 begin 157 if @PageNumber=1 158 set @SqlTotalCount=@SqlDistinct+' TOP '+ CAST(@PageSize as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName 159 +' WHERE 1=1 and ' + @QueryCondition + ' ORDER BY '+ @SortFields +' '+ @SortTypeB 160 else if(@EnabledSort=1) 161 begin 162 set @SqlTotalCount=@SqlDistinct+' TOP '+ CAST(@PageSize as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName 163 +' WHERE '+@Primarykey+' <(SELECT MIN('+ @Primarykey +') FROM ('+ @SqlDistinct+' TOP '+ CAST(@PageSize*(@PageNumber-1) as Varchar(20)) +' '+ @Primarykey +' FROM '+@TableName 164 +' WHERE (1=1) and ' + @QueryCondition +' ORDER BY '+ @SortFields +' '+ @SortTypeB+') AS TBMinID)'+' ORDER BY '+ @SortFields +' '+ @SortTypeB 165 end 166 else 167 begin 168 set @SqlTotalCount=@SqlDistinct+' TOP '+ CAST(@PageSize as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName 169 +' WHERE '+@Primarykey+' >(SELECT MAX('+ @Primarykey +') FROM ('+ @SqlDistinct+' TOP '+ CAST(@PageSize*(@PageNumber-1) as Varchar(20)) +' '+ @Primarykey +' FROM '+@TableName 170 +' WHERE (1=1) and ' + @QueryCondition +' ORDER BY '+ @SortFields +' '+ @SortTypeB+') AS TBMinID)'+' ORDER BY '+ @SortFields +' '+ @SortTypeB 171 end 172 end 173 else 174 begin 175 set @PageNumber = @pageIndex-@PageNumber+1 --后半部分数据处理 176 if @PageNumber <= 1 --最后一页数据显示 177 set @SqlTotalCount=@SqlDistinct+' * FROM ('+@SqlDistinct+' TOP '+ CAST(@lastcount as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName 178 +' WHERE (1=1) and '+ @QueryCondition +' ORDER BY '+ @SortFields +' '+ @SortTypeA+') AS TempTB'+' ORDER BY '+ @SortFields +' '+ @SortTypeB 179 else if(@EnabledSort=1) 180 set @SqlTotalCount=@SqlDistinct+' * FROM ('+@SqlDistinct+' TOP '+ CAST(@PageSize as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName 181 +' WHERE '+@Primarykey+' >(SELECT MAX('+ @Primarykey +') FROM('+ @SqlDistinct+' TOP '+ CAST(@PageSize*(@PageNumber-2)+@lastcount as Varchar(20)) +' '+ @Primarykey +' FROM '+@TableName 182 +' WHERE (1=1) and '+ @QueryCondition +' ORDER BY '+ @SortFields +' '+ @SortTypeA+') AS TBMaxID)'+' ORDER BY '+ @SortFields +' '+ @SortTypeA+') AS TempTB'+' ORDER BY '+ @SortFields +' '+ @SortTypeB 183 else 184 set @SqlTotalCount=@SqlDistinct+' * FROM ('+@SqlDistinct+' TOP '+ CAST(@PageSize as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName 185 +' WHERE '+@Primarykey+' <(SELECT MIN('+ @Primarykey +') FROM('+ @SqlDistinct+' TOP '+ CAST(@PageSize*(@PageNumber-2)+@lastcount as Varchar(20)) +' '+ @Primarykey +' FROM '+@TableName 186 +' WHERE (1=1) and '+ @QueryCondition +' ORDER BY '+ @SortFields +' '+ @SortTypeA+') AS TBMaxID)'+' ORDER BY '+ @SortFields +' '+ @SortTypeA+') AS TempTB'+' ORDER BY '+ @SortFields +' '+ @SortTypeB 187 end 188 end 189 190 ------返回查询结果----- 191 exec sp_executesql @SqlTotalCount 192 --SELECT datediff(ms,@timediff,getdate()) as 耗时 193 print @SqlTotalCount 194 SET NOCOUNT OFF