Get correlative types by type
2008-08-13 16:17 Komici 阅读(197) 评论(0) 编辑 收藏 举报set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
create PROCEDURE [dbo].[Oud_FindPlanSonTypesIDS]
(
@TypeID uniqueidentifier
)
AS
BEGIN
WITH SUB_TABLE ( FTypeID , FParentID , FName)
AS
(
SELECT
FTypeID ,
FParentID ,
FName
FROM O_TeachingPlanTypes
WHERE FTypeID = @TypeID
UNION ALL
SELECT
PRIOR_TAB.FTypeID ,
PRIOR_TAB.FParentID ,
PRIOR_TAB.FName
FROM
(
SELECT
FTypeID ,
FParentID ,
FName
FROM O_TeachingPlanTypes
)
PRIOR_TAB ,
SUB_TABLE
--WHERE SUB_TABLE.FParentID = PRIOR_TAB.FTypeID (Get all parentTypes by current type)
WHERE SUB_TABLE.FTypeID = PRIOR_TAB.FParentID --(Get all sonTypes by current type)
)
SELECT * FROM SUB_TABLE
END