title: SQL Server : CTE (recursion), set condition outside the CTE
body: <p>Using the query below (I'm new to CTE, so this might be messy), I want to build a reverse category path of all the final categories in my database.</p>
  
  <p>At the moment I have a single ID defined within the CTE itself (268707). Is there a way during my final main query that when I reference the CTE I can provide a variable?</p>
  
  <p>I've put a comment in the query where I assume would be the ideal place to do this, but for the life of me can't figure out how.</p>
  
  <pre><code>WITH recursion(stocknode_id, short_desc, parentnode_id, level) AS
  (
  SELECT
      stocknode_id,
      short_desc,
      parentnode_id,
      0 AS level
  
  FROM stock_website_node AS swn
  
  WHERE swn.stocknode_id = 268707
  
  UNION ALL
  
  SELECT
      swn.stocknode_id,
      swn.short_desc,
      swn.parentnode_id,
      h.level + 1
  
  FROM stock_website_node AS swn
  
  INNER JOIN recursion AS h ON h.parentnode_id = swn.stocknode_id
  )
  
  SELECT
  TOP 1 STUFF
  (
      (
          SELECT ' / ' + swn_stuff.short_desc
          FROM stock_website_node AS swn_stuff
          WHERE swn_stuff.stocknode_id IN (SELECT stocknode_id FROM recursion) /* &lt;--- I want to pass the variable from here, I think... */
          FOR XML PATH(''), TYPE
      ).value('.','varchar(max)'),
      1, 2, ''
   ) AS category_tree
  
  FROM stock_website_node AS swn_a
  </code></pre>
  
  <p>What I'd ideally like is something that would produce results if I was to do something similar too (but doesn't appear to work):</p>
  
  <pre><code>WITH recursion(stocknode_id, short_desc, parentnode_id, level) AS
  (
      SELECT
          stocknode_id,
          short_desc,
          parentnode_id,
          0 AS level
  
      FROM stock_website_node AS swn
  
      UNION ALL
  
      SELECT
          swn.stocknode_id,
          swn.short_desc,
          swn.parentnode_id,
          h.level + 1
  
      FROM stock_website_node AS swn
  
      INNER JOIN recursion AS h ON h.parentnode_id = swn.stocknode_id
  )
  
  SELECT
      TOP 1 STUFF
      (
          (
              SELECT ' / ' + swn_stuff.short_desc
              FROM stock_website_node AS swn_stuff
              WHERE swn_stuff.stocknode_id IN (SELECT x.stocknode_id FROM recursion AS x WHERE x.xtocknode_id = swn_a.stocknode_id)
              FOR XML PATH(''), TYPE
          ).value('.','varchar(max)'),
          1, 2, ''
       ) AS category_tree
  
  FROM stock_website_node AS swn_a
  
  WHERE swn_a = 268707 OR swn_a = 268708 OR swn_a = 268709
  </code></pre>
  
  <p>I've created a codepad with the SQL to create a table variable so you can see exactly what I'm doing if you'd like to run the code (<a href="http://codepad.org/4AaZEnlq" rel="nofollow">http://codepad.org/4AaZEnlq</a>).</p>
  
  <p>The table holds categories, and parentnode_id refers to the stocknode_id of the parent category. A parentnode_id of 0 means this is a top level category. The variable provided currently in the CTE is a bottom level category, and the query overall builds a category path as the result, such as:</p>
  
  <p>Top Level / Second Level / Third Level / Fourth Level</p>
  
  <p>During the main query I want to provide several bottom level categories and return the category path for each one, but with the current limitation of only being able to manually define a single bottom level ID rather than dynamically provide several, I'm a little stuck.</p>