树状接口Sql分页

public static DataSet GetListByPage(string where, int pageSize, int currentIndex)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat(@"declare @CurrentPageIndex INT,@PageSize INT;
                            set @CurrentPageIndex={0}
                            set @PageSize={1}
                            ;with A as (
                            select * from (
                            select Row_Number() over(order by FN_UNIT_NAME) RowNo,* from SYS_CONSTRUCTION_UNIT where           FN_ID_PARENT='00000000-0000-0000-0000-000000000000'",currentIndex, pageSize);
            if (!string.IsNullOrEmpty(where.Trim()))
            {
                sql.Append(" and " + where);
            }
            sql.Append(@" ) a where RowNo>=cast(@CurrentPageIndex*@PageSize+1 AS NVARCHAR) and RowNo <=CAST           ((@CurrentPageIndex+1)*@PageSize AS NVARCHAR)
                            UNION ALL 
                             SELECT Row_Number() over(order by B.FN_UNIT_NAME) RowNo,B.* FROM SYS_CONSTRUCTION_UNIT B,A WHERE                              B.FN_ID_PARENT=A.FN_ID_UNIT
                            ) 
                            SELECT * FROM ( SELECT SC.*,S1.FN_NAME FN_CLIENT_TYPE_NAME,S2.FN_NAME FN_CLIENT_STATUS_NAME,
                            S3.FN_NAME FN_CLIENT_FROM_NAME,S4.FN_NAME FN_CLIENT_ISSHARE_NAME FROM 
                            (SELECT * FROM A) SC
                            LEFT JOIN (SELECT FN_CODE,FN_NAME FROM SYS_DOMAIN WHERE FN_DOMAIN_NAME='FN_CLIENT_TYPE') S1
                            ON SC.FN_CLIENT_TYPE=S1.FN_CODE
                            LEFT JOIN (SELECT FN_CODE,FN_NAME FROM SYS_DOMAIN WHERE FN_DOMAIN_NAME='FN_CLIENT_STATUS') S2
                            ON SC.FN_CLIENT_STATUS=S2.FN_CODE
                            LEFT JOIN (SELECT FN_CODE,FN_NAME FROM SYS_DOMAIN WHERE FN_DOMAIN_NAME='FN_CLIENT_FROM') S3
                            ON SC.FN_CLIENT_FROM=S3.FN_CODE
                            LEFT JOIN (SELECT FN_CODE,FN_NAME FROM SYS_DOMAIN WHERE FN_DOMAIN_NAME='FN_CLIENT_ISSHARE') S4           ON SC.FN_CLIENT_ISSHARE=S4.FN_CODE
                            )TB ORDER BY FN_ORDER ASC;select COUNT(*) 总行数 from SYS_CONSTRUCTION_UNIT where                            FN_ID_PARENT='00000000-0000-0000-0000-000000000000'");
            DataSet dt = SqlDataHelper.Query(sql.ToString());
            return dt;
        }