SQL2005递归查询+存储过程分页

一、资讯类别表ArticleType

资讯表Article

二、递归查询一

with

table1 as

(

    select * from ArticleTypewhere Id=1 --定位点成员（也就是初始值或第一个结果集）

    union all

    select a.* from ArticleType ainner join table1on a.ParentId=table1.Id--递归成员

)

select* from table1

结果1
三、递归查询二

with

table1 as

(

    select * from ArticleTypewhere Id=4 --定位点成员（也就是初始值或第一个结果集）

    union all

    select a.* from ArticleType ainner join table1on a.Id=table1.ParentId--递归成员

)

select* from table1

结果2

四、递归查询结合分页存储过程

alterprocedure UP_Article_ListOfPre_select

@PageIndex int=1,--当前页码

@PageSize int=10,--每页大小

@TypeName nvarchar(20)='彩票资讯',--资讯类别名称

@RecordCount int=0 output--当前条件下的总记录数

as

begin

declare @StartIndexint,@EndIndex int

declare @Idint

set @StartIndex=(@PageIndex-1)*@PageSize

set @EndIndex=@StartIndex+@PageSize-1

select @Id=Idfrom ArticleType where TypeName=@TypeName

 

;with

table1 as

(

    select Id from ArticleType where Id=@Id --定位点成员（也就是初始值或第一个结果集）

    union all

    select a.Id from ArticleType ainner join table1on a.ParentId=table1.Id--递归成员

)

-- select * from table1 --CTE with之后第一句必须使用CTE的select。CTE的生命周期只是在第一次使用之后就消亡。

select Idinto #mytb from table1

--获取当前条件下的总记录数

select @RecordCount=count(Id)from Article where TypeIdin(select Id from #mytb)

--分页查询

select* from

(

    select row_number() over(orderby AddTime desc)as Row,Id, Title,AddTimefrom Article

    where TypeId in(select Idfrom #mytb)

) awhere Row between @StartIndexand @EndIndex

end

 

分页结果

五、C#调用代码

///<summary>

        ///根据条件分页查询信息

        ///</summary>

        ///<param name="pageIndex">当前页码</param>

        ///<param name="pageSize">每页大小</param>

        ///<param name="typeName">资讯类别名称</param>

        ///<param name="recordCount">out参数，当前条件下的总记录数</param>

        ///<returns>分页查询后的列表</returns>

        public DataTable GetList(int pageIndex, int pageSize, string typeName, out int recordCount)

        {

            SqlParameter[] parms = {

                                       new SqlParameter("@PageIndex",SqlDbType.Int),

                                       new SqlParameter("@PageSize",SqlDbType.Int),

                                       new SqlParameter("@TypeName",SqlDbType.NVarChar,20),

                                       new SqlParameter("@RecordCount",SqlDbType.Int)

                                   };

            parms[0].Value = pageIndex;

            parms[1].Value = pageSize;

            parms[2].Value = typeName;

            parms[3].Direction = ParameterDirection.Output;

            DataTable dt = SQLHelper.GetDataTable(CommandType.StoredProcedure, "UP_Article_ListOfPre_select", parms);

            recordCount = Convert.ToInt32(parms[3].Value);

            return dt;

        }