Using a recursive CTE in a view

Question

Having a typical parent/child hierarchy table it's a common thing to query it using Common Table Expression:

 with CTE  as (
    select Id, ProviderId, ConsumerId
    from T1
    where ProviderId in (2, 3, 9)

    union all
    select T1.Id, T1.ProviderId, T1.ConsumerId
    from T1  
    join CTE on C.ProviderId = CTE.ConsumerId 
)
select * from CTE

Is it possible to create a view based on this query so that one can do:

select * from MagicView where ProviderId in (2,3,9)

In other words, can we somehow extract parameters from the anchor part of the CTE to create a generic view?

Answer 1

Create a TVF:

CREATE FUNCTION my_function (
    @ProviderId int
)
RETURNS @ProviderTable TABLE 
(
    Id int NULL, 
    ProviderId int NULL,
    ConsumerId int NULL
)
AS 
BEGIN
    WITH cte AS (
        SELECT  Id, 
                ProviderId, 
                ConsumerId
        FROM T1
        WHERE ProviderId in (@ProviderId)
        UNION ALL
        SELECT  t.Id, 
                t.ProviderId, 
                t.ConsumerId
        FROM T1 t 
        INNER JOIN cte c 
            ON t.ProviderId = c.ConsumerId 
    )

    INSERT INTO @ProviderTable
    SELECT * FROM cte;

    RETURN;
END;

Than create a view:

CREATE VIEW my_view
AS 
SELECT m.*
FROM Providers p
CROSS APPLY my_function (p.ProviderId) m

After that you can SELECT from view whatever you need:

SELECT * 
FROM my_view 
WHERE ProviderId in (2,3,9)
OPTION (MAXRECURSION 0)