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

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

<script type="text/javascript"></script> <script class="blogstory"></script>

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

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