2分法分页存储过程脚本实例_mssql数据库教程-查字典教程网

摘要：需要说明的是：这个存储过程参数比较多，我再实际使用中又在外面单独写了一个类，页面调用直接调用封装的类，方法有很多，主要是思路，大家可以参考下...

需要说明的是：这个存储过程参数比较多，我再实际使用中又在外面单独写了一个类，页面调用直接调用封装的类，方法有很多，主要是思路，大家可以参考下。

代码修改集中在类似

复制代码代码如下:

if @Sort=0

set @strTmp = @strTmp + '<(select min('

else

set @strTmp = @strTmp + '>(select max('

另外94行主要是配合我自己写的类，显示记录条数分页数等信息，如果不需要就去掉。

复制代码代码如下:

1ALTER PROCEDURE [dbo].[proc_ListPage]

3 @tblName nvarchar(200), ----要显示的表或多个表的连接

4 @fldName nvarchar(500) = '*', ----要显示的字段列表

5 @pageSize int = 10, ----每页显示的记录个数

6 @page int = 1, ----要显示那一页的记录

7 @fldSort nvarchar(200) = null, ----排序字段列表或条件

8 @Sort bit = 0, ----排序方法，0为升序，1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如：' SortA Asc,SortB Desc,SortC ')

9 @strCondition nvarchar(1000) = null, ----查询条件,不需where

10 @ID nvarchar(150), ----主表的主键

11 @Dist bit = 0, ----是否添加查询字段的 DISTINCT 默认0不添加/1添加

12 @pageCount int = 1 output, ----查询结果分页后的总页数

13 @Counts int = 1 output ----查询到的记录数

14 )

15 AS

16 SET NOCOUNT ON

17 Declare @sqlTmp nvarchar(1000) ----存放动态生成的SQL语句

18 Declare @strTmp nvarchar(1000) ----存放取得查询结果总数的查询语句

19 Declare @strID nvarchar(1000) ----存放取得查询开头或结尾ID的查询语句

21 Declare @strSortType nvarchar(10) ----数据排序规则A

22 Declare @strFSortType nvarchar(10) ----数据排序规则B

24 Declare @SqlSelect nvarchar(50) ----对含有DISTINCT的查询进行SQL构造

25 Declare @SqlCounts nvarchar(50) ----对含有DISTINCT的总数查询进行SQL构造

28 if @Dist = 0

29 begin

30 set @SqlSelect = 'select '

31 set @SqlCounts = 'Count(0)'

32 end

33 else

34 begin

35 set @SqlSelect = 'select distinct '

36 set @SqlCounts = 'Count(DISTINCT '+@ID+')'

37 end

40 if @Sort=0

41 begin

42 set @strFSortType=' ASC '

43 set @strSortType=' DESC '

44 end

45 else

46 begin

47 set @strFSortType=' DESC '

48 set @strSortType=' ASC '

49 end

53 --------生成查询语句--------

54 --此处@strTmp为取得查询结果数量的语句

55 if @strCondition is null or @strCondition='' --没有设置显示条件

56 begin

57 set @sqlTmp = @fldName + ' From ' + @tblName

58 set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName

59 set @strID = ' From ' + @tblName

60 end

61 else

62 begin

63 set @sqlTmp = + @fldName + 'From ' + @tblName + ' where (1>0) ' + @strCondition

64 set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName + ' where (1>0) ' + @strCondition

65 set @strID = ' From ' + @tblName + ' where (1>0) ' + @strCondition

66 end

68 ----取得查询结果总数量-----

69 exec sp_executesql @strTmp,N'@Counts int out ',@Counts out

70 declare @tmpCounts int

71 if @Counts = 0

72 set @tmpCounts = 1

73 else

74 set @tmpCounts = @Counts

76 --取得分页总数

77 set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize

79 /**//**当前页大于总页数取最后一页**/

80 if @page>@pageCount

81 set @page=@pageCount

83 --/*-----数据分页2分处理-------*/

84 declare @pageIndex int --总数/页大小

85 declare @lastcount int --总数%页大小

87 set @pageIndex = @tmpCounts/@pageSize

88 set @lastcount = @tmpCounts%@pageSize

89 if @lastcount > 0

90 set @pageIndex = @pageIndex + 1

91 else

92 set @lastcount = @pagesize

94 --为配合显示

95 set nocount off

96 select @page curpage,@pageSize pagesize,@pageCount countpage,@tmpCounts [Rowcount]

97 set nocount on

99 --//***显示分页

100 if @strCondition is null or @strCondition='' --没有设置显示条件

101 begin

102 if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理

103 begin

104 if @page=1

105 set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(40))+' '+ @fldName+' from '+@tblName

106 +' order by '+ @fldSort +' '+ @strFSortType

107 else

108 begin

109 set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(40))+' '+ @fldName+' from '+@tblName

110 +' where '+@ID

111 if @Sort=0

112 set @strTmp = @strTmp + '>(select max('

113 else

114 set @strTmp = @strTmp + '<(select min('

115 set @strTmp = @strTmp + @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName

116 +' order by '+ @fldSort +' '+ @strFSortType+') AS TBMinID)'

117 +' order by '+ @fldSort +' '+ @strFSortType

118 end

119 end

120 else

121

122 begin

123 set @page = @pageIndex-@page+1 --后半部分数据处理

124 if @page <= 1 --最后一页数据显示

125 set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(40))+' '+ @fldName+' from '+@tblName

126 +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType

127 else

128 begin

129 set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(40))+' '+ @fldName+' from '+@tblName

130 +' where '+@ID

131 if @Sort=0

132 set @strTmp=@strTmp+' <(select min('

133 else

134 set @strTmp=@strTmp+' >(select max('

135 set @strTmp=@strTmp+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName

136 +' order by '+ @fldSort +' '+ @strSortType+') AS TBMaxID)'

137 +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType

138 end

139 end

140

141 end

142

143 else --有查询条件

144 begin

145 if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理

146 begin

147 if @page=1

148 set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(40))+' '+ @fldName+' from '+@tblName

149 +' where 1=1 ' + @strCondition + ' order by '+ @fldSort +' '+ @strFSortType

150 else

151 begin

152 set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(40))+' '+ @fldName+' from '+@tblName

153 +' where '+@ID

154 if @Sort=0

155 set @strTmp = @strTmp + '>(select max('

156 else

157 set @strTmp = @strTmp + '<(select min('

158

159 set @strTmp = @strTmp + @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName

160 +' where (1=1) ' + @strCondition +' order by '+ @fldSort +' '+ @strFSortType+') AS TBMinID)'

161 +' '+ @strCondition +' order by '+ @fldSort +' '+ @strFSortType

162 end

163 end

164 else

165 begin

166 set @page = @pageIndex-@page+1 --后半部分数据处理

167 if @page <= 1 --最后一页数据显示

168 set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(40))+' '+ @fldName+' from '+@tblName

169 +' where (1=1) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType

170 else

171 begin

172 set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(40))+' '+ @fldName+' from '+@tblName

173 +' where '+@ID

174 if @Sort=0

175 set @strTmp = @strTmp + '<(select min('

176 else

177 set @strTmp = @strTmp + '>(select max('

178 set @strTmp = @strTmp + @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName

179 +' where (1=1) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TBMaxID)'

180 +' '+ @strCondition+' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType

181 end

182 end

183

184 end

185

186 ------返回查询结果-----

187 SET NOCOUNT off

188 exec sp_executesql @strTmp

189 print @strTmp