WITH FilteredRoots AS (SELECT [Id]
FROM [dbo].[ProjectItems]
WHERE [WorkspaceId] = @WorkspaceId
  AND [ProjectKey] = @ProjectKey
  AND (@Type IS NULL
   OR [Type] = @Type)
  AND (@Status IS NULL
   OR [Status] = @Status))
    , ItemHierarchy AS (
SELECT pi.[Id], pi.[Id] AS [RootId], pi.[WorkspaceId], pi.[ProjectKey], pi.[Title], pi.[Description], pi.[Type], pi.[Status], pi.[State], pi.[Estimate], pi.[Priority], pi.[AssignedTo], pi.[CustomFields], pi.[Labels], pi.[AcceptanceCriteria], pi.[ParentId], pi.[Decision]
FROM [dbo].[ProjectItems] AS pi
    INNER JOIN FilteredRoots AS roots
ON roots.[Id] = pi.[Id]

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
ORDER BY [RootId],
    CASE
    WHEN [ParentId] IS NULL THEN 0
    ELSE 1
END
, [ParentId], [Title];
