存储过程递归查询及分页示例

数据表【CN_New_Category】截图：

 

 
存储过程：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        ZHAOWEI
-- Create date: 20120410
-- Description:    选取新闻中心所有类别下的数据
-- =============================================
--exec CN_New_GetAllByCategoryID 20,10,0
alter PROCEDURE CN_New_GetAllByCategoryID
(
   
    @CategoryID        int,
    @PageSize        INT,
    @PageIndex        INT
)
AS
BEGIN
    SET NOCOUNT ON;
   
    DECLARE @strStartRow    NVARCHAR(50)
    DECLARE @strEndRow        NVARCHAR(50)
    declare @RecordTable table
    (
        [id] int
      ,[categoryid] int
      ,[title] nvarchar(500)
      ,[titlecolor] varchar(100)
      ,[isbold] int
      ,[content] ntext
      ,[contentpagecount] int
      ,[titlelink] varchar(255)
      ,[image] varchar(50)
      ,[imagelink] varchar(150)
      ,[tempstate] int
      ,[skin] int
      ,[status] int
      ,[publishdate] datetime
      ,[validata] datetime
      ,[newtodata] datetime
      ,[createdate] datetime
      ,[writedate] datetime
      ,[inforesource] varchar(50)
      ,[newskey1] nvarchar(100)
      ,[newskey2] nvarchar(100)
      ,[newskey3] nvarchar(100)
      ,[productkey1] nvarchar(100)
      ,[productkey2] nvarchar(100)
      ,[productkey3] nvarchar(100)
      ,[ordernumber] int
      ,[newsfrom] varchar(500)
      ,[viewcount] int
      ,[item1] varchar(100)
      ,[item2] varchar(100)
      ,[item3] varchar(100)
    )

    SET  @strStartRow = @PageIndex*@PageSize + 1;
    SET  @strEndRow   = @PageIndex*@PageSize + @PageSize;

    with Categorys(ID,ParentID,CategoryID1,CategoryID2,CategoryID3,CategoryID4,CategoryName,LevelID,CategoryURL,
    Status,BGColor,BGImage,TempState,OrderNumber,TitleColor,IsBold)
    as
    (
        select * from CN_New_Category where LevelID=1 and id=@CategoryID
        union all
        select SC.* FROM CN_New_Category SC
            INNER JOIN Categorys C ON SC.ParentID=C.ID
    )
   
    insert into @RecordTable
    select * from CN_New_Info where categoryid in
    (
    select id from Categorys
    )
    SELECT T.*
    FROM
    (
    select row_number() over (order by createdate desc) as SNO,* from @RecordTable
    ) AS T
    WHERE T.SNO>=@strStartRow AND T.SNO<=@strEndRow
   
    SELECT COUNT(ID) AS recordcount FROM @RecordTable
END
GO


测试运行结果截图：