Select Tree

SelectTree.sql

CREATE PROCEDURE [dbo].[ClientHierarchy]
    @clientId UNIQUEIDENTIFIER
AS

DECLARE @root UNIQUEIDENTIFIER;


-- step 1, find hightest node
WITH ParentCTE (Id, ParentClientId) AS
(
   SELECT [Id], [ParentClientId]
   FROM [dbo].[Client]
   WHERE Id = @clientId

   UNION ALL
   
   -- Recursive up tree
   SELECT r.[Id], r.[ParentClientId]
   FROM [dbo].[Client] r
      INNER JOIN ParentCTE eh ON r.[Id] = eh.[ParentClientId] 
)

SELECT @root = [Id]
FROM ParentCTE
WHERE [ParentClientId] IS NULL;

-- step 2, select full tree
WITH ClientCTE (Id, ParentClientId) AS
(
   SELECT [Id], [ParentClientId]
   FROM [dbo].[Client]
   WHERE Id = @root

   UNION ALL

   -- Recursive down tree
   SELECT r.[Id], r.[ParentClientId]
   FROM [dbo].[Client] r
      INNER JOIN ClientCTE eh ON r.[ParentClientId] = eh.[Id]
)

SELECT 
    cte.[Id], 
    cte.[ParentClientId],
    a.[Name],   
FROM ClientCTE as cte
    INNER JOIN [Client] a on cte.[Id] = a.[Id]