需要说明的是：这个存储过程参数比较多，我再实际使用中又在外面单独写了一个类，页面调用直接调用封装的类，方法有很多，主要是思路，大家可以参考下。 代码修改集中在类似
需要说明的是：这个存储过程参数比较多，我再实际使用中又在外面单独写了一个类，页面调用直接调用封装的类，方法有很多，主要是思路，大家可以参考下。
代码修改集中在类似复制代码 代码如下:if @Sort=0set @strTmp = @strTmp + '<(select min(' elseset @strTmp = @strTmp + '>(select max('
另外94行主要是配合我自己写的类，显示记录条数分页数等信息，如果不需要就去掉。
复制代码 代码如下: 1ALTER PROCEDURE [dbo].[proc_ListPage] 2( 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的查询语句 20 21 Declare @strSortType nvarchar(10) ----数据排序规则A 22 Declare @strFSortType nvarchar(10) ----数据排序规则B 23 24 Declare @SqlSelect nvarchar(50) ----对含有DISTINCT的查询进行SQL构造 25 Declare @SqlCounts nvarchar(50) ----对含有DISTINCT的总数查询进行SQL构造 26 27 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 38 39 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 50 51 52 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 67 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 75 76 --取得分页总数 77 set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize 78 79 /**//**当前页大于总页数 取最后一页**/ 80 if @page>@pageCount 81 set @page=@pageCount 82 83 --/*-----数据分页2分处理-------*/ 84 declare @pageIndex int --总数/页大小 85 declare @lastcount int --总数%页大小 86 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 93 94 --为配合显示 95 set nocount off 96 select @page curpage,@pageSize pagesize,@pageCount countpage,@tmpCounts [Rowcount] 97 set nocount on 98 99 --//***显示分页100 if @strCondition is null or @strCondition='' --没有设置显示条件101 begin102 if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理103 begin 104 if @page=1105 set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(40))+' '+ @fldName+' from '+@tblName 106 +' order by '+ @fldSort +' '+ @strFSortType107 else108 begin 109 set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(40))+' '+ @fldName+' from '+@tblName110 +' where '+@ID111 if @Sort=0112 set @strTmp = @strTmp + '>(select max('113 else114 set @strTmp = @strTmp + '<(select min('115 set @strTmp = @strTmp + @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName116 +' order by '+ @fldSort +' '+ @strFSortType+') AS TBMinID)'117 +' order by '+ @fldSort +' '+ @strFSortType118 end 119 end120 else121 122 begin123 set @page = @pageIndex-@page+1 --后半部分数据处理124 if @page <= 1 --最后一页数据显示 125 set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(40))+' '+ @fldName+' from '+@tblName126 +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType 127 else128 begin129 set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(40))+' '+ @fldName+' from '+@tblName130 +' where '+@ID131 if @Sort=0132 set @strTmp=@strTmp+' <(select min('133 else134 set @strTmp=@strTmp+' >(select max('135 set @strTmp=@strTmp+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName136 +' order by '+ @fldSort +' '+ @strSortType+') AS TBMaxID)'137 +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType 138 end139 end140 141 end142 143 else --有查询条件144 begin145 if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理146 begin147 if @page=1148 set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(40))+' '+ @fldName+' from '+@tblName 149 +' where 1=1 ' + @strCondition + ' order by '+ @fldSort +' '+ @strFSortType150 else151 begin 152 set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(40))+' '+ @fldName+' from '+@tblName153 +' where '+@ID154 if @Sort=0155 set @strTmp = @strTmp + '>(select max('156 else157 set @strTmp = @strTmp + '<(select min('158 159 set @strTmp = @strTmp + @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName160 +' where (1=1) ' + @strCondition +' order by '+ @fldSort +' '+ @strFSortType+') AS TBMinID)'161 +' '+ @strCondition +' order by '+ @fldSort +' '+ @strFSortType162 end 163 end164 else165 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 '+@tblName169 +' where (1=1) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType 170 else171 begin172 set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(40))+' '+ @fldName+' from '+@tblName173 +' where '+@ID174 if @Sort=0175 set @strTmp = @strTmp + '<(select min('176 else177 set @strTmp = @strTmp + '>(select max('178 set @strTmp = @strTmp + @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName179 +' 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 end185 186 ------返回查询结果-----187 SET NOCOUNT off188 exec sp_executesql @strTmp189 print @strTmp
