Hierarchical structure for concrete id

Question

I have a recursive database table and hierarchical view through all table data

CREATE VIEW dbo.vw_hierarchicalView
AS
    WITH hView (Id,
                    IdParent,
                    Level)
    AS
    (
        SELECT tableParent.Id,
               tableParent.IdParent,
               1 AS Level
        FROM dbo.vw_ComplaintWithStatus tableParent
        WHERE tableParent.IdParent IS NULL
        UNION ALL SELECT tableChild.Id,
                         tableChild.IdParent,
                         hw.level + 1 AS  Level
                 FROM dbo.vw_ComplaintWithStatus tableChild
                      INNER JOIN hView hw ON  tableChild.IdParent = hw.Id
    )
    SELECT final.Id,
           final.IdParent,
           ISNULL(final.Level, 1) AS Level
    FROM hView final

When I do query for all data, all tree is right.

SELECT * FROM dbo.vw_hierarchicalView hw ORDER BY hw.Level, hw.Id

But, if i want to select full tree only for one id, query show me only first row with specified id

SELECT * FROM dbo.vw_hierarchicalView hw WHERE hw.Id = 5 ORDER BY hw.Level, hw.Id

I want to use this in my application to find all children records in tree using Entity Framework.

Logic of application returns me some record and need to find:

1. first direct children records only
2. all children records (for showing tree in web page)

I class to select from view to a stored procedure with Id as argument of this procedure and replace condition WHERE tableParent.IdParent IS NULL on WHERE tableParent.Id = @id. This solution works well, but ...

I rather don't want to use a stored procedure.

Is any way how to solve this without database functions or procedures?

Answer 1

You could use table valued function:

CREATE FUNCTION my_func(@root_id INT)
RETURNS TABLE
AS
RETURN
WITH hView (Id,IdParent,Level) AS
    (
        SELECT tableParent.Id,
               tableParent.IdParent,
               1 AS Level
        FROM dbo.vw_ComplaintWithStatus tableParent
        WHERE (tableParent.IdParent IS NULL  AND @root_id IS NULL)
           OR Id = @root_id
        UNION ALL SELECT tableChild.Id,
                         tableChild.IdParent,
                         hw.level + 1 AS  Level
                 FROM dbo.vw_ComplaintWithStatus tableChild
                      INNER JOIN hView hw ON  tableChild.IdParent = hw.Id
    )
    SELECT final.Id,
           final.IdParent,
           ISNULL(final.Level, 1) AS Level
    FROM hView final;

Call:

SELECT * FROM dbo.my_func(NULL) hw ORDER BY hw.Level, hw.Id;
--
SELECT * FROM dbo.my_func(5) hw ORDER BY hw.Level, hw.Id;