数据表【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
测试运行结果截图: