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