现在的位置: 首页 > 综合 > 正文

一个成熟的 多字段 排序 分页 存储过程 (有bug)

2013年10月19日 ⁄ 综合 ⁄ 共 6950字 ⁄ 字号 小 中 大 ⁄ 评论关闭

 

同事那的一个成熟 多字段 排序 分页 存储过程

 

 

sql中调用 ：

 

exec CMS_SearchGetDataByPage 'tblschool','id,IsEmp,IsNom','IsEmp , IsNom','IsEmp desc, IsNom desc','id',10,1,''

 

 

 

 

c# 中调用：

 

  1. /// <summary>
  2.         /// 分页获取数据列表2
  3.         /// </summary>
  4.         /// <param name="tableName">表名</param>
  5.         /// <param name="fldNames">选择字段列表，以,分隔</param>
  6.         /// <param name="selectOrderFldName">排序字段列表，以,分隔（不能含keyFldName指定的字段，可为空）</param>
  7.         /// <param name="orderFldDesc">排序字段及方向，如addDate desc,id desc（排序字段需通过selectOrderFldName指
  9. 定，可为空）</param>
  10.         /// <param name="keyFldName">主键字段（没有主键请指定不重复的字段）</param>
  11.         /// <param name="PageSize">每页记录数</param>
  12.         /// <param name="PageIndex">当前页</param>
  13.         /// <param name="strWhere">SQL条件</param>
  14.         /// <returns>数据列表</returns>
  15.         public static DataSet GetList(string tableName, string fldNames, string selectOrderFldName, string 
  17. orderFldDesc, string keyFldName, int PageSize, int PageIndex, string strWhere)
  18.         {
  19.             SqlParameter[] parameters = {
  20.                     new SqlParameter("@tblName", SqlDbType.VarChar, 255),
  21.                     new SqlParameter("@fldNames", SqlDbType.VarChar, 1000),
  22.                     new SqlParameter("@selectOrderFldName", SqlDbType.VarChar, 500),
  23.                     new SqlParameter("@orderFldDesc", SqlDbType.VarChar, 500),
  24.                     new SqlParameter("@keyFldName", SqlDbType.VarChar, 255),
  25.                     new SqlParameter("@PageSize", SqlDbType.Int),
  26.                     new SqlParameter("@PageIndex", SqlDbType.Int),
  27.                     new SqlParameter("@strWhere", SqlDbType.VarChar,1000)
  28.                     };
  29.             parameters[0].Value = tableName;
  30.             parameters[1].Value = fldNames;
  31.             parameters[2].Value = selectOrderFldName;
  32.             parameters[3].Value = orderFldDesc;
  33.             parameters[4].Value = keyFldName;
  34.             parameters[5].Value = PageSize;
  35.             parameters[6].Value = PageIndex;
  36.             parameters[7].Value = strWhere;
  37.             return DbHelperSQL.RunProcedure("CMS_SearchGetDataByPage", parameters, "ds");
  38.         }

存储过程本身：

 

  2. create PROCEDURE CMS_SearchGetDataByPage
  3.     @tblName      varchar(255),       -- 表名
  4.     @fldNames      varchar(1000),     -- 选择的字段列表以,分隔
  5.     @selectOrderFldName      varchar(500),  -- 排序字段以,分隔（不能含keyFldName指定的字段，可为空）
  6.     @orderFldDesc      varchar(500),  -- 排序字段及排序方向，如addDate desc,id desc（排序字段需通过selectOrderFldName指定，可为空）
  7.     @keyFldName      varchar(255),     -- 主键字段
  8.     @PageSize     int = 10,           -- 页尺寸
  9.     @PageIndex    int = 1,            -- 页码
  10.     @strWhere     varchar(1000) = ''  -- 查询条件(注意: 不要加where)
  11. AS
  12. declare @strWhereA varchar(1200)            -- 临时变量，给sqlwhere加where
  13. declare @strOrderA varchar(2000)            -- 第一次排序类型
  14. declare @strOrderB varchar(2000)            -- 第二次排序类型
  15. declare @strSqlA varchar(4000)          -- 第一次选出
  16. declare @strSqlB varchar(8000)          -- 第二次选出
  17. declare @strSQL varchar(8000)           -- 最后选出
  18. /* 条件*/
  19. if @strWhere != ''
  20.     set @strWhereA = ' where ' + @strWhere
  21. else
  22.     set @strWhereA = ''
  23.     
  24. /* 选择字段列表*/
  25. if @fldNames is null or rtrim(@fldNames) = ''
  26.     set @fldNames = '*'
  27.     
  28. /* 排序字段列表*/
  29. if not(@selectOrderFldName is null or rtrim(@selectOrderFldName) = '')
  30.     if rtrim(@selectOrderFldName) = 'id'
  31.         set @selectOrderFldName = ''
  32.     else
  33.         set @selectOrderFldName = ',' + @selectOrderFldName
  34.     
  35. /* 构建order，按指定方式排序*/
  36. if @orderFldDesc is null or rtrim(@orderFldDesc) = ''
  37.     set @orderFldDesc = ' order by id desc'
  38. else
  39.     set @orderFldDesc = ' order by ' + @orderFldDesc
  40. set @strOrderA = UPPER(@orderFldDesc)
  41. set @strOrderB =  replace(@strOrderA,'DESC','DESC1')
  42. set @strOrderB =  replace(@strOrderB,'ASC','DESC')
  43. set @strOrderB =  replace(@strOrderB,'DESC1','ASC')
  45. /* 第一页*/
  46. if @PageIndex = 1
  47.     set @strSQL = 'select top ' + str(@PageSize) + ' ' + @fldNames + ' from [' + @tblName + '] with(nolock)' + @strWhereA + ' ' + @strOrderA
  48. else
  49.     begin
  50.         --取得总记录数
  51.         declare @sql nvarchar(500)
  52.         declare @maxCount int
  53.         declare @maxPage int
  54.         declare @tempRowCount int
  55.         set @sql ='select @maxCount = count('+@keyFldName+') from [' + @tblName + ']' + @strWhereA
  56.         exec sp_executesql @sql,N'@maxCount int output',@maxCount output
  57.         set @maxPage = @maxCount / @PageSize
  58.         if(@maxCount % @PageSize > 0)
  59.             set @maxPage = @maxPage + 1
  60.         /* 最后一页*/
  61.         if @PageIndex >= @maxPage
  62.             begin
  63.                 set @PageIndex = @maxPage
  64.                 set @strSqlA = char(13) + '(select top '+str(@maxCount % @PageSize)+' ' + @keyFldName + @selectOrderFldName + ' from [' + @tblName + '] as a with(nolock) ' + @strWhereA + @strOrderB + '  )' + char(13)
  65.                 set @strSqlB = char(13) + '(select ' + @keyFldName +' from ' + @strSqlA  + ' as b )' + char(13)
  66.                 set @strSQL = 'select ' + @fldNames + ' from [' + @tblName + '] where ([' + @keyFldName + '] in '+@strSqlB+')' + @strOrderA + char(13)
  67.             end
  68.         else
  69.             begin
  70.                 /* 不是第一页，也不是最后一页*/
  71.                 if(@PageIndex <= @maxPage / 2)
  72.                     begin
  73.                     --前半数的页
  74.                         set @tempRowCount = @PageIndex * @PageSize
  75.                         /* 构建SQL，本分页算法的目的是为了实现高效的非主键排序的分页。by tony */
  76.                         /* 1、先按指定字段+主键字段按降序选出perPage*pageNum条记录*/
  77.                         set @strSqlA = char(13) + '(select top '+str(@tempRowCount)+' ' + @keyFldName + @selectOrderFldName + ' from [' + @tblName + '] as a  with(nolock) ' + @strWhereA + @strOrderA + '  )' + char(13)
  78.                         /* 2、再从选出的记录中按升序选出perPage条记录*/
  79.                         set @strSqlB = char(13) + '(select top '+str(@PageSize)+' ' + @keyFldName + ' from ' + @strSqlA  + ' as b ' + @strOrderB + '  )' + char(13)
  80.                         /* 3、从数据库中选出主键在第二次选出的记录中的记录，按降序排列，分页完成*/
  81.                         set @strSQL = 'select ' + @fldNames + ' from [' + @tblName + '] where ([' + @keyFldName + '] in '+@strSqlB+')' + @strOrderA
  82.                     end
  83.                 else
  84.                     begin
  85.                     --后半数的页
  86.                         set @tempRowCount = @maxCount - (@PageIndex -1) * @PageSize
  87.                         /* 构建SQL，本分页算法的目的是为了实现高效的非主键排序的分页。by tony */
  88.                         /* 1、先按指定字段+主键字段按降序选出perPage*pageNum条记录*/
  89.                         set @strSqlA = char(13) + '(select top '+str(@tempRowCount)+' ' + @keyFldName + @selectOrderFldName + ' from [' + @tblName + '] as a  with(nolock) ' + @strWhereA + @strOrderB + '  )' + char(13)
  90.                         /* 2、再从选出的记录中按升序选出perPage条记录*/
  91.                         set @strSqlB = char(13) + '(select top '+str(@PageSize)+' ' + @keyFldName + ' from ' + @strSqlA  + ' as b ' + @strOrderA + '  )' + char(13)
  92.                         /* 3、从数据库中选出主键在第二次选出的记录中的记录，按降序排列，分页完成*/
  93.                         set @strSQL = 'select ' + @fldNames + ' from [' + @tblName + '] where ([' + @keyFldName + '] in '+@strSqlB+')' + @strOrderA
  94.                     end
  95.                 end
  96.     end
  98. set nocount on
  100. /*print @strSQL*/ --显示SQL
  101. exec (@strSQL)
  102. set nocount off
  103. RETURN
返回
【上篇】
【下篇】

作者:

抱歉!评论已关闭.

返回首页

Copyright © 2013-2018 学步园  保留所有权利.
软文销售 QQ客服：2265327166 点击这里给我发消息（其他合作也可洽谈）
必威体育
必威电竞