CREATE FUNCTION [dbo].[GetAllChildrenTypeById]

(

    @id varchar(50)

)

RETURNS TABLE 

AS

RETURN

with cte as

(

 SELECT [id]

      ,[dictionaryTypeCode]

      ,[text]

      ,[parentId]

      ,[sort]

      ,[availabl]

      ,[leaf]

      ,[url]

      ,[value] FROM [Dictionary] where ID = @id

union all

SELECT a.[id],a.[dictionaryTypeCode],a.[text],a.[parentId],a.[sort],a.[availabl],a.[leaf],a.[url],a.[value] FROM [Dictionary] a  join cte b on a.[parentId] = b.Id

)

select id from cte 

GO