分页存储过程UP_GetRecordByPage
1 ------------------------------------
2 --用途：支持任意排序的分页存储过程
3 --说明：
4 ------------------------------------
5
6 CREATE PROCEDURE [dbo].[UP_GetRecordByPageOrder]
7
8 @tblName varchar(255), -- 表名
9 @fldName varchar(255), -- 显示字段名
10 @OrderfldName varchar(255), -- 排序字段名
11 @StatfldName varchar(255), -- 统计字段名
12 @PageSize int = 10, -- 页尺寸
13 @PageIndex int = 1, -- 页码
14 @IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回
15 @OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
16 @strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where)
17 AS
18
19 declare @strSQL varchar(6000) -- 主语句
20 declare @strTmp varchar(100) -- 临时变量(查询条件过长时可能会出错，可修改100为1000)
21 declare @strOrder varchar(400) -- 排序类型
22
23 if @OrderType != 0
24 begin
25 set @strTmp = '<(select min'
26 set @strOrder = ' order by [' + @OrderfldName +'] desc'
27 end
28 else
29 begin
30 set @strTmp = '>(select max'
31 set @strOrder = ' order by [' + @OrderfldName +'] asc'
32 end
33
34 set @strSQL = 'select top ' + str(@PageSize) + ' ' + @fldName + ' from ['
35 + @tblName + '] where [' + @OrderfldName + ']' + @strTmp + '(['
36 + @OrderfldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
37 + @OrderfldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
38 + @strOrder
39
40 if @strWhere != ''
41 set @strSQL = 'select top ' + str(@PageSize) + ' ' + @fldName + ' from ['
42 + @tblName + '] where [' + @OrderfldName + ']' + @strTmp + '(['
43 + @OrderfldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
44 + @OrderfldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
45 + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
46
47 if @PageIndex = 1
48 begin
49 set @strTmp = ''
50 if @strWhere != ''
51 set @strTmp = ' where ' + @strWhere
52
53 set @strSQL = 'select top ' + str(@PageSize) + ' ' + @fldName + ' from ['
54 + @tblName + ']' + @strTmp + ' ' + @strOrder
55 end
56
57
58 if @IsReCount != 0
59 set @strSQL = @strSQL+' select count(1) as Total from [' + @tblName + ']'
60
61 if @strWhere!=''
62 set @strSQL = @strSQL+' where ' + @strWhere
63 exec (@strSQL)
64
65
66
67
68 ------------------------------------
69 --用途：分页存储过程(对有主键的表效率极高)
70 --说明：
71 ------------------------------------
72
73 CREATE PROCEDURE [dbo].[UP_GetRecordByPage]
74 @tblName varchar(255), -- 表名
75 @fldName varchar(255), -- 主键字段名
76 @PageSize int = 10, -- 页尺寸
77 @PageIndex int = 1, -- 页码
78 @IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回
79 @OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
80 @strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where)
81 AS
82
83 declare @strSQL varchar(6000) -- 主语句
84 declare @strTmp varchar(100) -- 临时变量(查询条件过长时可能会出错，可修改100为1000)
85 declare @strOrder varchar(400) -- 排序类型
86
87 if @OrderType != 0
88 begin
89 set @strTmp = '<(select min'
90 set @strOrder = ' order by [' + @fldName +'] desc'
91 end
92 else
93 begin
94 set @strTmp = '>(select max'
95 set @strOrder = ' order by [' + @fldName +'] asc'
96 end
97
98 set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
99 + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
100 + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
101 + @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
102 + @strOrder
103
104 if @strWhere != ''
105 set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
106 + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
107 + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
108 + @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
109 + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
110
111 if @PageIndex = 1
112 begin
113 set @strTmp =''
114 if @strWhere != ''
115 set @strTmp = ' where ' + @strWhere
116
117 set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
118 + @tblName + ']' + @strTmp + ' ' + @strOrder
119 end
120
121 if @IsReCount != 0
122 set @strSQL = 'select count(*) as Total from [' + @tblName + ']'+' where ' + @strWhere
123
124 exec (@strSQL)
2 --用途：支持任意排序的分页存储过程
3 --说明：
4 ------------------------------------
5
6 CREATE PROCEDURE [dbo].[UP_GetRecordByPageOrder]
7
8 @tblName varchar(255), -- 表名
9 @fldName varchar(255), -- 显示字段名
10 @OrderfldName varchar(255), -- 排序字段名
11 @StatfldName varchar(255), -- 统计字段名
12 @PageSize int = 10, -- 页尺寸
13 @PageIndex int = 1, -- 页码
14 @IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回
15 @OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
16 @strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where)
17 AS
18
19 declare @strSQL varchar(6000) -- 主语句
20 declare @strTmp varchar(100) -- 临时变量(查询条件过长时可能会出错，可修改100为1000)
21 declare @strOrder varchar(400) -- 排序类型
22
23 if @OrderType != 0
24 begin
25 set @strTmp = '<(select min'
26 set @strOrder = ' order by [' + @OrderfldName +'] desc'
27 end
28 else
29 begin
30 set @strTmp = '>(select max'
31 set @strOrder = ' order by [' + @OrderfldName +'] asc'
32 end
33
34 set @strSQL = 'select top ' + str(@PageSize) + ' ' + @fldName + ' from ['
35 + @tblName + '] where [' + @OrderfldName + ']' + @strTmp + '(['
36 + @OrderfldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
37 + @OrderfldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
38 + @strOrder
39
40 if @strWhere != ''
41 set @strSQL = 'select top ' + str(@PageSize) + ' ' + @fldName + ' from ['
42 + @tblName + '] where [' + @OrderfldName + ']' + @strTmp + '(['
43 + @OrderfldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
44 + @OrderfldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
45 + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
46
47 if @PageIndex = 1
48 begin
49 set @strTmp = ''
50 if @strWhere != ''
51 set @strTmp = ' where ' + @strWhere
52
53 set @strSQL = 'select top ' + str(@PageSize) + ' ' + @fldName + ' from ['
54 + @tblName + ']' + @strTmp + ' ' + @strOrder
55 end
56
57
58 if @IsReCount != 0
59 set @strSQL = @strSQL+' select count(1) as Total from [' + @tblName + ']'
60
61 if @strWhere!=''
62 set @strSQL = @strSQL+' where ' + @strWhere
63 exec (@strSQL)
64
65
66
67
68 ------------------------------------
69 --用途：分页存储过程(对有主键的表效率极高)
70 --说明：
71 ------------------------------------
72
73 CREATE PROCEDURE [dbo].[UP_GetRecordByPage]
74 @tblName varchar(255), -- 表名
75 @fldName varchar(255), -- 主键字段名
76 @PageSize int = 10, -- 页尺寸
77 @PageIndex int = 1, -- 页码
78 @IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回
79 @OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
80 @strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where)
81 AS
82
83 declare @strSQL varchar(6000) -- 主语句
84 declare @strTmp varchar(100) -- 临时变量(查询条件过长时可能会出错，可修改100为1000)
85 declare @strOrder varchar(400) -- 排序类型
86
87 if @OrderType != 0
88 begin
89 set @strTmp = '<(select min'
90 set @strOrder = ' order by [' + @fldName +'] desc'
91 end
92 else
93 begin
94 set @strTmp = '>(select max'
95 set @strOrder = ' order by [' + @fldName +'] asc'
96 end
97
98 set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
99 + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
100 + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
101 + @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
102 + @strOrder
103
104 if @strWhere != ''
105 set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
106 + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
107 + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
108 + @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
109 + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
110
111 if @PageIndex = 1
112 begin
113 set @strTmp =''
114 if @strWhere != ''
115 set @strTmp = ' where ' + @strWhere
116
117 set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
118 + @tblName + ']' + @strTmp + ' ' + @strOrder
119 end
120
121 if @IsReCount != 0
122 set @strSQL = 'select count(*) as Total from [' + @tblName + ']'+' where ' + @strWhere
123
124 exec (@strSQL)
