Using sql with in where clause

Question

How can i use with in a query like this? Is there a way to use the tableIds in TableList? Can i treat them like a subquery?

select * from TestTable where tableId in 

(WITH TableList (tableId)
AS
(
SELECT tableId FROM OldTable WHERE tableId=5 and IsDeleted = 0
UNION ALL
SELECT t.tableId From OldTable as t
INNER JOIN TableList as p
ON t.ParenTableId = p.TableId
WHERE t.IsDeleted = 0
)
SELECT tableId FROM TableList
)

EDIT: To show why i cant use with in first statement of sql.

select TableId, IsDeleted,
( 
  select count(OtherId) OtherTable
  where otherTableId in 
  (WITH TableList (tableId)
  AS
  (
  SELECT tableId FROM OldTable WHERE tableId=5 and IsDeleted = 0
  UNION ALL
  SELECT t.tableId From OldTable as t
  INNER JOIN TableList as p
  ON t.ParenTableId = p.TableId
  WHERE t.IsDeleted = 0
  )
  SELECT tableId FROM TableList
  )
  ) as Ex1
from TestTable where tableId in (1,2,3)

I think this show why im trying to use with clause in the middle of a sql query.

Answer 1

You need to define your WITH CTE before calling the SQL:

WITH TableList (tableId)
AS
(
    SELECT tableId FROM OldTable WHERE tableId=5 and IsDeleted = 0
    UNION ALL
    SELECT t.tableId From OldTable as t
        INNER JOIN TableList as p
            ON t.ParenTableId = p.TableId
    WHERE t.IsDeleted = 0
)
SELECT * 
FROM TestTable 
WHERE tableId in 
(
    SELECT tableId FROM TableList
)

EDIT -- Based on your comments, you could define your Recursive CTE in a VIEW and then use as needed:

CREATE VIEW YourView AS 
WITH TableList (tableId)
AS
(
    SELECT tableId FROM OldTable WHERE tableId=5 and IsDeleted = 0
    UNION ALL
    SELECT t.tableId From OldTable as t
        INNER JOIN TableList as p
            ON t.ParenTableId = p.TableId
    WHERE t.IsDeleted = 0
)
SELECT * 
FROM TableList;