I am using a recursive CTE on a tree structure to list all the descendants of a particular node in the tree. If I write a literal node value in my `WHERE` clause, SQL Server seems to actually apply the CTE just to that value, giving a query plan with [low actual rows counts, et cetera](https://www.brentozar.com/pastetheplan/?id=HJ0ImE5Hr):

[![query plan with literal value](https://i.stack.imgur.com/uHkeY.png)](https://i.stack.imgur.com/uHkeY.png)

However, if I pass the value as a parameter, it seems to [realize (spool) the CTE and then filter it after the fact](https://www.brentozar.com/pastetheplan/?id=B1h7m4qBr):

[![query plan with parameter value](https://i.stack.imgur.com/04yqe.png)](https://i.stack.imgur.com/04yqe.png)

I could be reading the plans wrong. I haven’t noticed a performance issue, but I am worried that realization of the CTE could cause issues with larger data sets, especially in a busier system. Also, I normally compound this traversal on itself: I traverse up to ancestors and back down to descendants (to ensure that I gather all related nodes). Due to how my data is, each set of “related” nodes is rather small, so realization of the CTE doesn’t make sense. And when SQL Server seems to realize the CTE, it is giving me some quite large numbers in its “actual” counts.

Is there a way to get the parameterized version of the query to act like the literal version? I want to put the CTE in a reusable view.

Query with literal:

```sql
CREATE PROCEDURE #c AS BEGIN;
    WITH descendants AS (SELECT
         t.ParentId Id
        ,t.Id DescendantId
    FROM #tree t
    WHERE t.ParentId IS NOT NULL
    UNION ALL SELECT
         d.Id
        ,t.Id DescendantId
    FROM descendants d
    JOIN #tree t ON d.DescendantId = t.ParentId)
    SELECT d.*
    FROM descendants d
    WHERE d.Id = 24
    ORDER BY d.Id, d.DescendantId;
END;
GO
EXEC #c;

```

Query with parameter:

```sql
CREATE PROCEDURE #c (@Id BIGINT) AS BEGIN;
    WITH descendants AS (SELECT
         t.ParentId Id
        ,t.Id DescendantId
    FROM #tree t
    WHERE t.ParentId IS NOT NULL
    UNION ALL SELECT
         d.Id
        ,t.Id DescendantId
    FROM descendants d
    JOIN #tree t ON d.DescendantId = t.ParentId)
    SELECT d.*
    FROM descendants d
    WHERE d.Id = @Id
    ORDER BY d.Id, d.DescendantId;
END;
GO
EXEC #c 24;

```

Setup code:

```sql
DECLARE @count BIGINT = 100000;
CREATE TABLE #tree (
     Id BIGINT NOT NULL PRIMARY KEY
    ,ParentId BIGINT
);
CREATE INDEX tree_23lk4j23lk4j ON #tree (ParentId);
WITH number AS (SELECT
         CAST(1 AS BIGINT) Value
    UNION ALL SELECT
         n.Value * 2 + 1
    FROM number n
    WHERE n.Value * 2 + 1 <= @count
    UNION ALL SELECT
         n.Value * 2
    FROM number n
    WHERE n.Value * 2 <= @count)
INSERT #tree (Id, ParentId)
SELECT n.Value, CASE WHEN n.Value % 3 = 0 THEN n.Value / 4 END
FROM number n;

```