(Common Table Expressions) CTE as part of WHERE clause... possible?

Is it possible to use CTE in a WHERE clause e.g.

SELECT * 
  FROM Table1 
 WHERE Table1.PK IN (
   WITH Cte AS (
    -- root selection (dynamic, generated in code)
    SELECT Bla FROM Table2开发者_如何转开发
    -- recursive part
    UNION ALL
    SELECT …..)
  SELECT Bla FROM Cte)

The reason I’m asking is that I need to use a recursive query and the only way of doing it at the moment without updating our framework is to place it in the where clause.

No, WITH clauses need to be defined before the main SELECT. Like this:

WITH recursive_cte AS (
   -- root selection (dynamic, generated in code)
   SELECT Bla FROM Table2
   -- recursive part
   UNION ALL
   SELECT …..)
SELECT t.*
  FROM TABLE1 t
  JOIN recursive_cte rc ON rc.key = t.pk

I also tweaked the query to use a JOIN instead, but you'll have to watch for duplicates.

Yes you can apply WHERE CLAUSE with CTE. You have to make Table-Valued Function and return result in Table. then you can use this result set in any query with WHERE Clause. Go to the link and find example:
http://muhammadnaveed.info/use-cte-query-in-where-clause/ Hope this will help you.