1 USE [dbo]
2 GO
3 /****** Object: StoredProcedure [dbo].[Common_GetPageRecords] Script Date: 2019-11-14 09:12:42 ******/
4 SET ANSI_NULLS ON
5 GO
6 SET QUOTED_IDENTIFIER ON
7 GO
8
9 /*
10 ** 获取指定页的记录。
11 **/
12 ALTER PROCEDURE [dbo].[Common_GetPageRecords]
13 @StartRow INT ,--起始行（从0开始）
14 @MaxRows INT ,--每页的最大记录数
15 @TableName NVARCHAR(2000) ,--表名
16 @PrimaryKey NVARCHAR(50) ,--主键
17 @GetFields NVARCHAR(1000) ,--要获取的列
18 @SearchConditions NVARCHAR(2000) ,--搜索条件
19 @SortExpression NVARCHAR(100) --排序表达式
20 AS /*默认排序*/
21 IF @SortExpression IS NULL
22 OR @SortExpression = ''
23 SET @SortExpression = REPLACE(REPLACE(@PrimaryKey, '[', ''), ']', '');
24
25 DECLARE @CurrentPage INT = 1;
26 DECLARE @SortTable VARCHAR(100);
27 DECLARE @SortName VARCHAR(100);
28 DECLARE @strSortColumn VARCHAR(200);
29 DECLARE @operator CHAR(2);
30 DECLARE @type VARCHAR(100);
31 DECLARE @prec INT;
32 DECLARE @sql NVARCHAR(1000);
33 DECLARE @paramDefine NVARCHAR(100);
34 DECLARE @TotalRecordCount INT;
35 DECLARE @RowCount INT;
36 DECLARE @Group VARCHAR(1000) = NULL;
37
38 IF @MaxRows = 0
39 BEGIN
40 RETURN
41 END
42 /******************** 数据表中总记录数 ***********************/
43 IF @PrimaryKey IS NOT NULL
44 AND @PrimaryKey <> ''
45 BEGIN
46 SET @PrimaryKey = REPLACE(REPLACE(@PrimaryKey, '[', ''), ']', '');
47 SET @sql = 'SELECT @RowCount = COUNT(1) FROM ' + @TableName
48 + ' WHERE '+@PrimaryKey+'<=0';
49 SET @paramDefine = '@RowCount INT OUTPUT';
50 EXEC sp_executesql @sql, @paramDefine, @RowCount OUTPUT;
51
52 IF @RowCount>0
53 BEGIN
54 IF @SearchConditions IS NOT NULL
55 AND @SearchConditions <> ''
56 BEGIN
57 SET @SearchConditions = @SearchConditions + ' AND '
58 + @PrimaryKey + ' !=-1 ';
59 END;
60 ELSE
61 BEGIN
62 SET @SearchConditions = @PrimaryKey + ' !=-1 ';
63 END;
64 END
65 END;
66
67 SET @sql = 'SELECT @TotalRecordCount = COUNT(*) FROM ' + @TableName;
68
69 IF @SearchConditions IS NOT NULL
70 AND @SearchConditions <> ''
71 BEGIN
72 SET @sql = @sql + ' WHERE ' + @SearchConditions;
73 END;
74
75 SET @paramDefine = '@TotalRecordCount INT OUTPUT';
76 EXEC sp_executesql @sql, @paramDefine, @TotalRecordCount OUTPUT;
77 /*************************************************************/
78
79 IF ( @StartRow = 0 )
80 SET @CurrentPage = 1;
81 IF @MaxRows = -1
82 BEGIN
83 SET @CurrentPage = 1;
84 SET @MaxRows = 10000000;
85 END;
86 ELSE
87 SET @CurrentPage = @StartRow / @MaxRows + 1;
88
89 /*设定排序语句.*/
90 IF CHARINDEX(' DESC', @SortExpression) > 0
91 BEGIN
92 SET @strSortColumn = REPLACE(@SortExpression, ' DESC', '');
93 SET @operator = '<=';
94 END;
95 ELSE
96 BEGIN
97 IF CHARINDEX(' ASC', @SortExpression) > 0
98 BEGIN
99 SET @strSortColumn = REPLACE(@SortExpression, ' ASC', '');
100 END
101 ELSE
102 BEGIN
103 SET @strSortColumn = @SortExpression;
104 END
105 SET @operator = '>=';
106 END;
107
108 IF CHARINDEX('.', @strSortColumn) > 0
109 BEGIN
110 SET @SortTable = SUBSTRING(@strSortColumn, 0,
111 CHARINDEX('.', @strSortColumn));
112 SET @SortName = SUBSTRING(@strSortColumn,
113 CHARINDEX('.', @strSortColumn) + 1,
114 LEN(@strSortColumn));
115 END;
116 ELSE
117 BEGIN
118 SET @strSortColumn = ISNULL(@strSortColumn, @PrimaryKey);
119 SET @SortTable = @TableName;
120 SET @SortName = @strSortColumn;
121 END;
122
123 -- table的内容还是要去掉dbo.和[]括号。
124 IF CHARINDEX('.', @SortTable) > 0 OR CHARINDEX('[', @SortTable) > 0
125 BEGIN
126 SET @SortTable =REPLACE(REPLACE(SUBSTRING(@SortTable, CHARINDEX('.', @SortTable) + 1,
127 LEN(@SortTable)),'[',''),']','')
128 END
129
130 SELECT @type = t.name ,
131 @prec = c.prec
132 FROM sysobjects o
133 JOIN syscolumns c ON o.id = c.id
134 JOIN systypes t ON c.xusertype = t.xusertype
135 WHERE o.name = @SortTable
136 AND c.name = @SortName;
137
138 --SELECT @SortTable tab,@SortName sort, @type tty, @prec prc
139 IF CHARINDEX('char', @type) > 0
140 BEGIN
141 IF @prec = -1 --如果排序的字段中有逗号存在时 @prec字段长度会为-1 就给它一个默认100长度
142 BEGIN
143 SET @type = @type + '(100)';
144 END
145 ELSE
146 BEGIN
147 SET @type = @type + '(' + CAST(@prec AS VARCHAR) + ')';
148 END
149 END
150
151 DECLARE @strPageSize VARCHAR(50);
152 DECLARE @strStartRow VARCHAR(50);
153 DECLARE @strFilter VARCHAR(1000);
154 DECLARE @strSimpleFilter VARCHAR(1000);
155 DECLARE @strGroup VARCHAR(1000);
156
157 /*设置分页参数.*/
158 SET @strPageSize = CAST(@MaxRows AS VARCHAR(50));
159 SET @strStartRow = CAST(( ( @CurrentPage - 1 ) * @MaxRows + 1 ) AS VARCHAR(50));
160 /*筛选以及分组语句.*/
161 IF @SearchConditions IS NOT NULL
162 AND @SearchConditions != ''
163 BEGIN
164 SET @strFilter = ' WHERE ' + @SearchConditions + ' ';
165 SET @strSimpleFilter = ' AND ' + @SearchConditions + ' ';
166 END;
167 ELSE
168 BEGIN
169 SET @strSimpleFilter = '';
170 SET @strFilter = '';
171 END;
172 IF @Group IS NOT NULL
173 AND @Group != ''
174 SET @strGroup = ' GROUP BY ' + @Group + ' ';
175 ELSE
176 SET @strGroup = '';
177
178 IF @type IS NULL
179 SET @type = 'NVARCHAR(100)';
180 --@SortColumn 为NULL 也要返回结果 20180911 Alma.Liu
181 PRINT '
182 DECLARE @SortColumn ' + @type + '
183 SET ROWCOUNT ' + @strStartRow + '
184 SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @TableName + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @SortExpression + '
185 SET ROWCOUNT ' + @strPageSize + '
186 IF @SortColumn IS NOT NULL
187 SELECT ' + @GetFields + ' FROM ' + @TableName + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @SortExpression + '
188 ELSE
189 SELECT ' + @GetFields + ' FROM ' + @TableName + ' WHERE 1=1 ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @SortExpression +'
190 '
191 /*执行查询语句*/
192 EXEC(
193 '
194 DECLARE @SortColumn ' + @type + '
195 SET ROWCOUNT ' + @strStartRow + '
196 SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @TableName + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @SortExpression + '
197 SET ROWCOUNT ' + @strPageSize + '
198 IF @SortColumn IS NOT NULL
199 SELECT ' + @GetFields + ' FROM ' + @TableName + ' WHERE ' + @strSortColumn + @operator +
200 ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @SortExpression + '
201 ELSE
202 SELECT ' + @GetFields + ' FROM ' + @TableName + ' WHERE 1=1 ' + @strSimpleFilter
203 + ' ' + @strGroup + ' ORDER BY ' + @SortExpression + '
204 '
205 );
206
207
208 RETURN @TotalRecordCount;
