title: SQL Server CTE select single tree branch structure upto the root
body: <p>Is it possible to pass a parameter into a CTE that selects a node then selects its parent up to the root where the <code>parentId</code> is null? </p>
  
  <p>In my code below if I pass in a parameter that selects Rain Coats and then recurses up the tree to mens wear where its <code>parentId</code> is null and selects all the nodes in that branch including children. Could someone help me with this please. My example just recurses and shows the depth</p>
  
  <p>SQL example:</p>
  
  <pre><code>DECLARE @Department TABLE
  (
      Id INT NOT NULL,
      Name varchar(50) NOT NULL,
      ParentId int NULL
  )
  
  INSERT INTO @Department SELECT 1, 'Toys', null
  INSERT INTO @Department SELECT 2, 'Computers', null
  INSERT INTO @Department SELECT 3, 'Consoles', 2
  INSERT INTO @Department SELECT 4, 'PlayStation 3', 3
  INSERT INTO @Department SELECT 5, 'Xbox 360', 2
  INSERT INTO @Department SELECT 6, 'Games', 1
  INSERT INTO @Department SELECT 7, 'Puzzles', 6
  INSERT INTO @Department SELECT 8, 'Mens Wear', null
  INSERT INTO @Department SELECT 9, 'Mens Clothing', 8
  INSERT INTO @Department SELECT 10, 'Jackets', 9
  INSERT INTO @Department SELECT 11, 'Rain Coats', 10
  
  ;WITH c 
  AS
  (
      SELECT Id, Name,1 AS Depth
      FROM @Department
      WHERE ParentId is null  
  
           UNION ALL
  
           SELECT t.Id, t.Name, c.Depth + 1 AS 'Level'
      FROM @Department T  
      JOIN c ON t.ParentId = c.Id
  
  )
  SELECT * FROM c WHERE c.Id = 3
  </code></pre>