WITH ItemHierarchy AS (SELECT [Id]
   , [Id] AS [RootId]
   , [WorkspaceId]
   , [ProjectKey]
   , [Title]
   , [Description]
   , [Type]
   , [Status]
   , [State]
   , [Estimate]
   , [Priority]
   , [AssignedTo]
   , [CustomFields]
   , [Labels]
   , [AcceptanceCriteria]
   , [ParentId]
   , [Decision]
FROM [dbo].[ProjectItems]
WHERE [Id] = @Id
  AND [WorkspaceId] = @WorkspaceId

UNION ALL

SELECT child.[Id],
       parent.[RootId],
       child.[WorkspaceId],
       child.[ProjectKey],
       child.[Title],
       child.[Description],
       child.[Type],
       child.[Status],
       child.[State],
       child.[Estimate],
       child.[Priority],
       child.[AssignedTo],
       child.[CustomFields],
       child.[Labels],
       child.[AcceptanceCriteria],
       child.[ParentId],
       child.[Decision]
FROM [dbo].[ProjectItems] AS child
    INNER JOIN ItemHierarchy AS parent
ON child.[ParentId] = parent.[Id]
   AND child.[WorkspaceId] = parent.[WorkspaceId])
SELECT [RootId], [Id], [WorkspaceId], [ProjectKey], [Title], [Description], [Type], [Status], [State], [Estimate], [Priority], [AssignedTo], [CustomFields], [Labels], [AcceptanceCriteria], [ParentId], [Decision]
FROM ItemHierarchy;
