sql server recursion recursive function example to get complex name string from parent and child in table hikel

USE [AccountingSqlTest]
GO
/****** Object:  UserDefinedFunction [dbo].[HIKEL_GET_NAME_BY_HIKEL_ID]    Script Date: 10/03/2015 22:37:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE  FUNCTION [dbo].[HIKEL_GET_NAME_BY_HIKEL_ID]
(
@HIKEL_ID INT
)
RETURNS NVARCHAR(1000)
AS
BEGIN
   DECLARE @HIKEL_NAME AS NVARCHAR(1000);
   --GET ALL ROWS RELATED PASSED HIKEL ID ACCORDING TO @HIKEL_ID PASSED
  WITH NAME_TREE AS (
   SELECT HIKEL_ID ,PARENT_ID, HIKEL_NAME,HIKEL_NAME_EN
   FROM HIKEL
   WHERE HIKEL_ID = @HIKEL_ID -- THIS IS THE STARTING POINT YOU WANT IN YOUR RECURSION SMALL HIKEL
   UNION ALL
   SELECT  C.HIKEL_ID ,C.PARENT_ID,C.HIKEL_NAME ,C.HIKEL_NAME_EN
   FROM HIKEL C
     JOIN NAME_TREE P ON P.PARENT_ID = C.HIKEL_ID  -- THIS IS THE RECURSION
)
SELECT DISTINCT @HIKEL_NAME= SUBSTRING(
(SELECT ','+ T.HIKEL_NAME AS [text()]

FROM NAME_TREE T
WHERE T.PARENT_ID IS NOT NULL
ORDER BY T.PARENT_ID ASC

FOR XML PATH ('')
), 2, 1000)


RETURN @HIKEL_NAME
END

----------------------------------------------------------------------------------------------------------------

create  FUNCTION [dbo].[HikelGetChilds]
(
@id INT
)
RETURNS table
AS
return (
WITH ret AS(
        SELECT  id ,ParentId, name ,MnagerId
        FROM    DEPARTMENTS
        WHERE   ID = @id
        UNION ALL
        SELECT  d.id ,d.ParentId,d. name ,d.MnagerId
        FROM    DEPARTMENTS d  INNER JOIN
                ret r ON d.ParentId = r.ID
)

SELECT  *
FROM    ret
)

-------------------------------------------------------------------------------------------------------------------------
ALTER  FUNCTION [dbo].[HikelGetParents]
(
@id INT
)
RETURNS table
AS
return (
   --GET ALL ROWS RELATED PASSED HIKEL ID ACCORDING TO @id PASSED
  WITH NAME_TREE AS (
   SELECT id ,ParentId, name ,MnagerId
   FROM DEPARTMENTS
   WHERE id = @id -- THIS IS THE STARTING POINT YOU WANT IN YOUR RECURSION SMALL HIKEL

   UNION ALL
   SELECT  C.id ,C.parentid,C.name  ,c.MnagerId
   FROM DEPARTMENTS C
     JOIN NAME_TREE P ON P.parentid = C.id  -- THIS IS THE RECURSION
)
SELECT DISTINCT *
 
   FROM NAME_TREE T
   WHERE T.parentid IS NOT NULL
   )
   

---------------------------------------------------------------------------------------------------------------------