Using a recursive CTE in a view
<p>Having a typical parent/child hierarchy table it's a common thing to query it using <a href="https://msdn.microsoft.com/en-us/library/ms175972.aspx" rel="nofollow">Common Table Expression</a>:</p>

<pre><code> 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
</code></pre>

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

<pre><code>select * from MagicView where ProviderId in (2,3,9)
</code></pre>

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