Parent Child CTE SQL Server

Parent_Child_CTE_SQL_Server.sql

CREATE TABLE #Organization(Id INT, ParentID INT, Name nvarchar(20));

INSERT INTO #Organization VALUES(1,NULL,'A');
INSERT INTO #Organization VALUES(2,NULL,'B');
INSERT INTO #Organization VALUES(3,NULL,'C');

INSERT INTO #Organization VALUES(4,1,'A->D');
INSERT INTO #Organization VALUES(5,2,'B->E');
INSERT INTO #Organization VALUES(6,3,'C->F');

INSERT INTO #Organization VALUES(7,4,'A->D->G');
INSERT INTO #Organization VALUES(8,5,'B->E->H');
INSERT INTO #Organization VALUES(9,6,'C->F->J');

INSERT INTO #Organization VALUES(10,7,'A->D->G->K');
INSERT INTO #Organization VALUES(11,8,'B->E->H->L');
INSERT INTO #Organization VALUES(12,9,'C->F->J->M');

DECLARE @Id int = 1;

--Get parents of @Id
WITH Parent AS (
		SELECT Id, ParentID, Name
		FROM #Organization
		WHERE Id = @Id
	UNION ALL
		SELECT a.Id, a.ParentID,a.Name
		FROM #Organization a
		INNER JOIN Parent B ON B.ID = A.ParentID
), 
	--Get Childs of @Id
Child AS (
		SELECT Id, ParentID, Name
		FROM #Organization
		WHERE Id = @Id 
	UNION ALL
		SELECT a.Id, a.ParentID, a.Name
		FROM #Organization A
		INNER JOIN Child B ON B.ParentID = A.ID
		)

	SELECT * FROM Parent
	UNION
	SELECT * FROM Child

	DROP TABLE #Organization
	
USE [MIS_HRApps]
GO
/****** Object:  UserDefinedFunction [dbo].[UFN_GET_ALL_CHILD_ORG]    Script Date: 7/2/2019 4:44:45 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER FUNCTION [dbo].[UFN_GET_ALL_CHILD_ORG]
(	
	@OrgID int
)
RETURNS TABLE 
AS
RETURN 
(
	WITH cte AS 
	 (
	  SELECT a.OrgId, a.ParentOrg_Id, a.OrgName
	  FROM T_COm_Master_Org a
	  WHERE OrgId = @OrgID and a.IsActive = 1
	  UNION ALL
	  SELECT a.OrgId, a.ParentOrg_Id, a.OrgName
	  FROM T_COm_Master_Org a JOIN cte c ON a.ParentOrg_Id = c.OrgId and a.IsActive = 1
	  )
	SELECT OrgId
	FROM cte
	UNION SELECT @OrgID	
)