1 set ANSI_NULLS ON
2
3 set QUOTED_IDENTIFIER ON
4 go
5 ALTER PROCEDURE [dbo].[PRO_PAGEDATA]
6 /*
7 ***************************************************************
8 ** 千万数量级分页存储过程 **
9 ***************************************************************
10 参数说明:
11 1.Tables :表名称,视图
12 2.PrimaryKey :主关键字
13 3.Sort :排序语句，不带Order By 比如：NewsID Desc,OrderRows Asc
14 4.CurrentPage :当前页码
15 5.PageSize :分页尺寸
16 6.Filter :过滤语句，不带Where
17 7.Group :Group语句,不带Group By
18 8.TotalCount 返回记录总数
19 ***************************************************************/
20 (
21 @Tables varchar ( 1000 ),
22 @PrimaryKey varchar ( 100 ),
23 @Sort varchar ( 200 ) = NULL ,
24 @CurrentPage int = 1 ,
25 @PageSize int = 10 ,
26 @Fields varchar ( 1000 ) = '*' ,
27 @Filter varchar ( 1000 ) = NULL ,
28 @Group varchar ( 1000 ) = NULL,
29 @TotalCount int output
30 )
31 AS
32
33 /*默认排序*/
34
35 IF @Sort IS NULL OR @Sort = ''
36 SET @Sort = @PrimaryKey
37 DECLARE @SortTable varchar ( 100 )
38 DECLARE @SortName varchar ( 100 )
39 DECLARE @strSortColumn varchar ( 200 )
40 DECLARE @operator char ( 2 )
41 DECLARE @type varchar ( 100 )
42 DECLARE @prec int
43
44 /*设定排序语句.*/
45
46 IF CHARINDEX( 'DESC' ,@Sort)> 0
47 BEGIN
48 SET @strSortColumn = REPLACE(@Sort, 'DESC' , '' )
49 SET @operator = '<='
50 END
51
52 ELSE
53 BEGIN
54 IF CHARINDEX( 'ASC' , @Sort) = 0
55 SET @strSortColumn = REPLACE(@Sort, 'ASC' , '' )
56 SET @operator = '>='
57 END
58
59 IF CHARINDEX( '.' , @strSortColumn) > 0
60 BEGIN
61 SET @SortTable = SUBSTRING(@strSortColumn, 0 , CHARINDEX( '.' ,@strSortColumn))
62 SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX( '.' ,@strSortColumn) + 1 , LEN(@strSortColumn))
63 END
64 ELSE
65 BEGIN
66 SET @SortTable = @Tables
67 SET @SortName = @strSortColumn
68 END
69 SELECT @type=t.name, @prec=c.prec
70 FROM sysobjects o
71 JOIN syscolumns c on o.id=c.id
72 JOIN systypes t on c.xusertype=t.xusertype
73 WHERE o.name = @SortTable AND c.name = @SortName
74 IF CHARINDEX( 'char' , @type) > 0
75 SET @type = @type + '(' + CAST (@prec AS varchar ) + ')'
76 DECLARE @strPageSize varchar ( 50 )
77 DECLARE @strStartRow varchar ( 50 )
78 DECLARE @strFilter varchar ( 1000 )
79 DECLARE @strSimpleFilter varchar ( 1000 )
80 DECLARE @strGroup varchar ( 1000 )
81
82 /*默认当前页*/
83 IF @CurrentPage < 1
84 SET @CurrentPage = 1
85 /*设置分页参数.*/
86 SET @strPageSize = CAST (@PageSize AS varchar ( 50 ))
87 SET @strStartRow = CAST (((@CurrentPage - 1 )*@PageSize + 1 ) AS varchar ( 50 ))
88
89 /*筛选以及分组语句.*/
90 declare @strSQL nvarchar(4000)
91
92 IF @Filter IS NOT NULL AND @Filter != ''
93 BEGIN
94 SET @strFilter = ' WHERE ' + @Filter + ' '
95 SET @strSimpleFilter = ' AND ' + @Filter + ' '
96 set @strSQL = ' select @TotalCount=count(*) from '+@Tables+' where '+@Filter+''
97 END
98
99 ELSE
100 BEGIN
101
102 SET @strSimpleFilter = ''
103 SET @strFilter = ''
104 set @strSQL = ' select @TotalCount=count(*) from '+@Tables+''
105 END
106 IF @Group IS NOT NULL AND @Group != ''
107 SET @strGroup = ' GROUP BY ' + @Group + ' '
108 ELSE
109 SET @strGroup = ''
110
111 --统计总条数
112
113 /*声明查询字符串*/
114
115 /*
116
117 set @strSQL='
118
119 DECLARE @SortColumn ' + @type + '
120
121 SET ROWCOUNT ' + @strStartRow + '
122
123 SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
124
125 SET ROWCOUNT ' + @strPageSize + '
126
127 SELECT ' + @Fields + 'FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
128
129 '
130
131 */
132
133 /*取得查询结果总数*/
134 exec sp_executesql
135 @strSQL,
136 N'@TotalCount int=0 OUTPUT',
137 @TotalCount=@TotalCount OUTPUT
138 /*执行查询语句*/
139
140 EXEC (
141 '
142 DECLARE @SortColumn ' + @type + '
143 SET ROWCOUNT ' + @strStartRow + '
144 SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
145 SET ROWCOUNT ' + @strPageSize + '
146 SELECT ' + @Fields + 'FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
147 '
148 )
2
3 set QUOTED_IDENTIFIER ON
4 go
5 ALTER PROCEDURE [dbo].[PRO_PAGEDATA]
6 /*
7 ***************************************************************
8 ** 千万数量级分页存储过程 **
9 ***************************************************************
10 参数说明:
11 1.Tables :表名称,视图
12 2.PrimaryKey :主关键字
13 3.Sort :排序语句，不带Order By 比如：NewsID Desc,OrderRows Asc
14 4.CurrentPage :当前页码
15 5.PageSize :分页尺寸
16 6.Filter :过滤语句，不带Where
17 7.Group :Group语句,不带Group By
18 8.TotalCount 返回记录总数
19 ***************************************************************/
20 (
21 @Tables varchar ( 1000 ),
22 @PrimaryKey varchar ( 100 ),
23 @Sort varchar ( 200 ) = NULL ,
24 @CurrentPage int = 1 ,
25 @PageSize int = 10 ,
26 @Fields varchar ( 1000 ) = '*' ,
27 @Filter varchar ( 1000 ) = NULL ,
28 @Group varchar ( 1000 ) = NULL,
29 @TotalCount int output
30 )
31 AS
32
33 /*默认排序*/
34
35 IF @Sort IS NULL OR @Sort = ''
36 SET @Sort = @PrimaryKey
37 DECLARE @SortTable varchar ( 100 )
38 DECLARE @SortName varchar ( 100 )
39 DECLARE @strSortColumn varchar ( 200 )
40 DECLARE @operator char ( 2 )
41 DECLARE @type varchar ( 100 )
42 DECLARE @prec int
43
44 /*设定排序语句.*/
45
46 IF CHARINDEX( 'DESC' ,@Sort)> 0
47 BEGIN
48 SET @strSortColumn = REPLACE(@Sort, 'DESC' , '' )
49 SET @operator = '<='
50 END
51
52 ELSE
53 BEGIN
54 IF CHARINDEX( 'ASC' , @Sort) = 0
55 SET @strSortColumn = REPLACE(@Sort, 'ASC' , '' )
56 SET @operator = '>='
57 END
58
59 IF CHARINDEX( '.' , @strSortColumn) > 0
60 BEGIN
61 SET @SortTable = SUBSTRING(@strSortColumn, 0 , CHARINDEX( '.' ,@strSortColumn))
62 SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX( '.' ,@strSortColumn) + 1 , LEN(@strSortColumn))
63 END
64 ELSE
65 BEGIN
66 SET @SortTable = @Tables
67 SET @SortName = @strSortColumn
68 END
69 SELECT @type=t.name, @prec=c.prec
70 FROM sysobjects o
71 JOIN syscolumns c on o.id=c.id
72 JOIN systypes t on c.xusertype=t.xusertype
73 WHERE o.name = @SortTable AND c.name = @SortName
74 IF CHARINDEX( 'char' , @type) > 0
75 SET @type = @type + '(' + CAST (@prec AS varchar ) + ')'
76 DECLARE @strPageSize varchar ( 50 )
77 DECLARE @strStartRow varchar ( 50 )
78 DECLARE @strFilter varchar ( 1000 )
79 DECLARE @strSimpleFilter varchar ( 1000 )
80 DECLARE @strGroup varchar ( 1000 )
81
82 /*默认当前页*/
83 IF @CurrentPage < 1
84 SET @CurrentPage = 1
85 /*设置分页参数.*/
86 SET @strPageSize = CAST (@PageSize AS varchar ( 50 ))
87 SET @strStartRow = CAST (((@CurrentPage - 1 )*@PageSize + 1 ) AS varchar ( 50 ))
88
89 /*筛选以及分组语句.*/
90 declare @strSQL nvarchar(4000)
91
92 IF @Filter IS NOT NULL AND @Filter != ''
93 BEGIN
94 SET @strFilter = ' WHERE ' + @Filter + ' '
95 SET @strSimpleFilter = ' AND ' + @Filter + ' '
96 set @strSQL = ' select @TotalCount=count(*) from '+@Tables+' where '+@Filter+''
97 END
98
99 ELSE
100 BEGIN
101
102 SET @strSimpleFilter = ''
103 SET @strFilter = ''
104 set @strSQL = ' select @TotalCount=count(*) from '+@Tables+''
105 END
106 IF @Group IS NOT NULL AND @Group != ''
107 SET @strGroup = ' GROUP BY ' + @Group + ' '
108 ELSE
109 SET @strGroup = ''
110
111 --统计总条数
112
113 /*声明查询字符串*/
114
115 /*
116
117 set @strSQL='
118
119 DECLARE @SortColumn ' + @type + '
120
121 SET ROWCOUNT ' + @strStartRow + '
122
123 SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
124
125 SET ROWCOUNT ' + @strPageSize + '
126
127 SELECT ' + @Fields + 'FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
128
129 '
130
131 */
132
133 /*取得查询结果总数*/
134 exec sp_executesql
135 @strSQL,
136 N'@TotalCount int=0 OUTPUT',
137 @TotalCount=@TotalCount OUTPUT
138 /*执行查询语句*/
139
140 EXEC (
141 '
142 DECLARE @SortColumn ' + @type + '
143 SET ROWCOUNT ' + @strStartRow + '
144 SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
145 SET ROWCOUNT ' + @strPageSize + '
146 SELECT ' + @Fields + 'FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
147 '
148 )