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
浙公网安备 33010602011771号