reference.ftv_RepoObject_ReferenceTree_referenced - IF

/*
--Duplicates are possible, if exists alternative path between objects with different depth
--to elimenate them, exclude Referenced_Depth and Referencing_Depth and use DISTINCT


<<property_start>>exampleUsage
DECLARE @RepoObject_guid uniqueidentifier

SET @RepoObject_guid = (SELECT RepoObject_guid from [repo].[RepoObject] where RepoObject_fullname = '[repo].[RepoObject_gross]')

SELECT *
FROM [reference].[ftv_RepoObject_ReferenceTree_referenced](@RepoObject_guid, DEFAULT, DEFAULT)
ORDER BY [Referenced_Depth]
 , [Referencing_Depth]

SELECT *
FROM [reference].[ftv_RepoObject_ReferenceTree_referenced](@RepoObject_guid, 1, 1)
ORDER BY [Referenced_Depth]
 , [Referencing_Depth]

SELECT *
FROM [reference].[ftv_RepoObject_ReferenceTree_referenced](@RepoObject_guid, 0, 6)
ORDER BY [Referenced_Depth]
 , [Referencing_Depth]

SELECT *
FROM [reference].[ftv_RepoObject_ReferenceTree_referenced](@RepoObject_guid, 100, 100)
ORDER BY [Referenced_Depth]
 , [Referencing_Depth]
<<property_end>>


*/
CREATE Function reference.ftv_RepoObject_ReferenceTree_referenced
(
    @RepoObject_guid   UniqueIdentifier
  , @Referenced_Depth  Int = 0
  , @Referencing_Depth Int = 0 --has no effect
)
Returns Table
As
Return
(
    --trees are recursive to get parent child relations
    With
    tree_referenced
    As
        (
        Select
            FirstNode.referenced_fullname
          , FirstNode.referenced_fullname2
          , FirstNode.Referenced_guid
          , FirstNode.referenced_is_DocsOutput
          , FirstNode.referenced_type
          , FirstNode.referencing_fullname
          , FirstNode.referencing_fullname2
          , FirstNode.Referencing_guid
          , FirstNode.referencing_type
          , FirstNode.referencing_is_DocsOutput
          , Referenced_Depth  = 1
          , Referencing_Depth = 0
        From
            reference.RepoObject_ReferencedReferencing As FirstNode
        --INNER JOIN [config].[type] t1
        -- ON t1.[type] = FirstNode.Referenced_type
        --INNER JOIN [config].[type] t2
        -- ON t2.[type] = FirstNode.Referencing_type
        Where
            FirstNode.Referencing_guid = @RepoObject_guid
            And 1                      <= @Referenced_Depth
        --AND t1.[is_DocsOutput] = 1
        --AND t2.[is_DocsOutput] = 1
        Union All
        Select
            child.referenced_fullname
          , child.referenced_fullname2
          , child.Referenced_guid
          , child.referenced_is_DocsOutput
          , child.referenced_type
          , child.referencing_fullname
          , child.referencing_fullname2
          , child.Referencing_guid
          , child.referencing_type
          , child.referencing_is_DocsOutput
          , Referenced_Depth = parent.Referenced_Depth + 1
          , 0
        From
            reference.RepoObject_ReferencedReferencing As child
            Inner Join
                tree_referenced                        As parent
                    On
                    child.Referencing_guid = parent.Referenced_guid
        --INNER JOIN [config].[type] t1
        -- ON t1.[type] = child.Referenced_type
        ----INNER JOIN [config].[type] t2
        ---- ON t2.[type] = child.Referencing_type
        Where
            parent.Referenced_Depth < @Referenced_Depth
        --AND t1.[is_DocsOutput] = 1
        ----AND t2.[is_DocsOutput] = 1
        )
    --,
    --  tree_referencing
    --  As
    --      (
    --      Select
    --          FirstNode.*
    --        , 0 As Referenced_Depth
    --        , 1 As Referencing_Depth
    --      From
    --          graph.RepoObject_ReferencingReferenced As FirstNode
    --      --INNER JOIN [config].[type] t1
    --      -- ON t1.[type] = FirstNode.Referenced_type
    --      --INNER JOIN [config].[type] t2
    --      -- ON t2.[type] = FirstNode.Referencing_type
    --      Where
    --          Referenced_guid = @RepoObject_guid
    --          And 1           <= @Referencing_Depth
    --      --AND t1.[is_DocsOutput] = 1
    --      --AND t2.[is_DocsOutput] = 1
    --      Union All
    --      Select
    --          child.*
    --        , 0
    --        , Referencing_Depth = parent.Referencing_Depth + 1
    --      From
    --          graph.RepoObject_ReferencingReferenced As child
    --          Inner Join
    --              tree_referencing                   As parent
    --                  On
    --                  child.Referenced_guid = parent.Referencing_guid
    --      ----INNER JOIN [config].[type] t1
    --      ---- ON t1.[type] = child.Referenced_type
    --      --INNER JOIN [config].[type] t2
    --      -- ON t2.[type] = child.Referencing_type
    --      Where
    --          parent.Referencing_Depth < @Referencing_Depth
    --      ----AND t1.[is_DocsOutput] = 1
    --      --AND t2.[is_DocsOutput] = 1
    --      )
    Select
        tree_referenced.referenced_fullname
      , tree_referenced.referenced_fullname2
      , tree_referenced.Referenced_guid
      , tree_referenced.referenced_is_DocsOutput
      , tree_referenced.referenced_type
      , tree_referenced.referencing_fullname
      , tree_referenced.referencing_fullname2
      , tree_referenced.Referencing_guid
      , tree_referenced.referencing_type
      , tree_referenced.referencing_is_DocsOutput
      , tree_referenced.Referenced_Depth
      , tree_referenced.Referencing_Depth
      , RepoObject_guid = @RepoObject_guid
    From
        tree_referenced
--Union
--Select
--    *
--  , @RepoObject_guid As RepoObject_guid
--From
--    tree_referencing
);