一个成熟的 多字段 排序 分页 存储过程 (有bug) 收藏
同事那的一个成熟 多字段 排序 分页 存储过程
sql中调用 ：
exec CMS_SearchGetDataByPage 'tblschool','id,IsEmp,IsNom','IsEmp , IsNom','IsEmp desc, IsNom desc','id',10,1,''
c# 中调用：
/// <summary>
/// 分页获取数据列表2
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="fldNames">选择字段列表，以,分隔</param>
/// <param name="selectOrderFldName">排序字段列表，以,分隔（不能含keyFldName指定的字段，可为空）</param>
/// <param name="orderFldDesc">排序字段及方向，如addDate desc,id desc（排序字段需通过selectOrderFldName指
定，可为空）</param>
/// <param name="keyFldName">主键字段（没有主键请指定不重复的字段）</param>
/// <param name="PageSize">每页记录数</param>
/// <param name="PageIndex">当前页</param>
/// <param name="strWhere">SQL条件</param>
/// <returns>数据列表</returns>
public static DataSet GetList(string tableName, string fldNames, string selectOrderFldName, string
orderFldDesc, string keyFldName, int PageSize, int PageIndex, string strWhere)
{
SqlParameter[] parameters = {
new SqlParameter("@tblName", SqlDbType.VarChar, 255),
new SqlParameter("@fldNames", SqlDbType.VarChar, 1000),
new SqlParameter("@selectOrderFldName", SqlDbType.VarChar, 500),
new SqlParameter("@orderFldDesc", SqlDbType.VarChar, 500),
new SqlParameter("@keyFldName", SqlDbType.VarChar, 255),
new SqlParameter("@PageSize", SqlDbType.Int),
new SqlParameter("@PageIndex", SqlDbType.Int),
new SqlParameter("@strWhere", SqlDbType.VarChar,1000)
};
parameters[0].Value = tableName;
parameters[1].Value = fldNames;
parameters[2].Value = selectOrderFldName;
parameters[3].Value = orderFldDesc;
parameters[4].Value = keyFldName;
parameters[5].Value = PageSize;
parameters[6].Value = PageIndex;
parameters[7].Value = strWhere;
return DbHelperSQL.RunProcedure("CMS_SearchGetDataByPage", parameters, "ds");
}
存储过程本身：
create PROCEDURE CMS_SearchGetDataByPage
@tblName varchar(255), -- 表名
@fldNames varchar(1000), -- 选择的字段列表以,分隔
@selectOrderFldName varchar(500), -- 排序字段以,分隔（不能含keyFldName指定的字段，可为空）
@orderFldDesc varchar(500), -- 排序字段及排序方向，如addDate desc,id desc（排序字段需通过selectOrderFldName指定，可为空）
@keyFldName varchar(255), -- 主键字段
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@strWhere varchar(1000) = '' -- 查询条件(注意: 不要加where)
AS
declare @strWhereA varchar(1200) -- 临时变量，给sqlwhere加where
declare @strOrderA varchar(2000) -- 第一次排序类型
declare @strOrderB varchar(2000) -- 第二次排序类型
declare @strSqlA varchar(4000) -- 第一次选出
declare @strSqlB varchar(8000) -- 第二次选出
declare @strSQL varchar(8000) -- 最后选出
/* 条件*/
if @strWhere != ''
set @strWhereA = ' where ' + @strWhere
else
set @strWhereA = ''
/* 选择字段列表*/
if @fldNames is null or rtrim(@fldNames) = ''
set @fldNames = '*'
/* 排序字段列表*/
if not(@selectOrderFldName is null or rtrim(@selectOrderFldName) = '')
if rtrim(@selectOrderFldName) = 'id'
set @selectOrderFldName = ''
else
set @selectOrderFldName = ',' + @selectOrderFldName
/* 构建order，按指定方式排序*/
if @orderFldDesc is null or rtrim(@orderFldDesc) = ''
set @orderFldDesc = ' order by id desc'
else
set @orderFldDesc = ' order by ' + @orderFldDesc
set @strOrderA = UPPER(@orderFldDesc)
set @strOrderB = replace(@strOrderA,'DESC','DESC1')
set @strOrderB = replace(@strOrderB,'ASC','DESC')
set @strOrderB = replace(@strOrderB,'DESC1','ASC')
/* 第一页*/
if @PageIndex = 1
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @fldNames + ' from [' + @tblName + '] with(nolock)' + @strWhereA + ' ' + @strOrderA
else
begin
--取得总记录数
declare @sql nvarchar(500)
declare @maxCount int
declare @maxPage int
declare @tempRowCount int
set @sql ='select @maxCount = count('+@keyFldName+') from [' + @tblName + ']' + @strWhereA
exec sp_executesql @sql,N'@maxCount int output',@maxCount output
set @maxPage = @maxCount / @PageSize
if(@maxCount % @PageSize > 0)
set @maxPage = @maxPage + 1
/* 最后一页*/
if @PageIndex >= @maxPage
begin
set @PageIndex = @maxPage
set @strSqlA = char(13) + '(select top '+str(@maxCount % @PageSize)+' ' + @keyFldName + @selectOrderFldName + ' from [' + @tblName + '] as a with(nolock) ' + @strWhereA + @strOrderB + ' )' + char(13)
set @strSqlB = char(13) + '(select ' + @keyFldName +' from ' + @strSqlA + ' as b )' + char(13)
set @strSQL = 'select ' + @fldNames + ' from [' + @tblName + '] where ([' + @keyFldName + '] in '+@strSqlB+')' + @strOrderA + char(13)
end
else
begin
/* 不是第一页，也不是最后一页*/
if(@PageIndex <= @maxPage / 2)
begin
--前半数的页
set @tempRowCount = @PageIndex * @PageSize
/* 构建SQL，本分页算法的目的是为了实现高效的非主键排序的分页。by tony */
/* 1、先按指定字段+主键字段按降序选出perPage*pageNum条记录*/
set @strSqlA = char(13) + '(select top '+str(@tempRowCount)+' ' + @keyFldName + @selectOrderFldName + ' from [' + @tblName + '] as a with(nolock) ' + @strWhereA + @strOrderA + ' )' + char(13)
/* 2、再从选出的记录中按升序选出perPage条记录*/
set @strSqlB = char(13) + '(select top '+str(@PageSize)+' ' + @keyFldName + ' from ' + @strSqlA + ' as b ' + @strOrderB + ' )' + char(13)
/* 3、从数据库中选出主键在第二次选出的记录中的记录，按降序排列，分页完成*/
set @strSQL = 'select ' + @fldNames + ' from [' + @tblName + '] where ([' + @keyFldName + '] in '+@strSqlB+')' + @strOrderA
end
else
begin
--后半数的页
set @tempRowCount = @maxCount - (@PageIndex -1) * @PageSize
/* 构建SQL，本分页算法的目的是为了实现高效的非主键排序的分页。by tony */
/* 1、先按指定字段+主键字段按降序选出perPage*pageNum条记录*/
set @strSqlA = char(13) + '(select top '+str(@tempRowCount)+' ' + @keyFldName + @selectOrderFldName + ' from [' + @tblName + '] as a with(nolock) ' + @strWhereA + @strOrderB + ' )' + char(13)
/* 2、再从选出的记录中按升序选出perPage条记录*/
set @strSqlB = char(13) + '(select top '+str(@PageSize)+' ' + @keyFldName + ' from ' + @strSqlA + ' as b ' + @strOrderA + ' )' + char(13)
/* 3、从数据库中选出主键在第二次选出的记录中的记录，按降序排列，分页完成*/
set @strSQL = 'select ' + @fldNames + ' from [' + @tblName + '] where ([' + @keyFldName + '] in '+@strSqlB+')' + @strOrderA
end
end
end
set nocount on
/*print @strSQL*/ --显示SQL
exec (@strSQL)
set nocount off
RETURN