Use CTE query in WHERE clause
Introduction
This post shows how to use Common Table Expressions (CTE ) in WHERE CLAUSE of a query.
Purpose
We have hierarchy of a category up to nth level and we have to apply any action i.e. update/delete for entire hierarchy. For this we use CTE query to get entire hierarchy but we can’t use CTE query in WHERE clause i.e. UPDATE tbl_Category SET isArchived = 1 WHERE Category IN(Result of CTE)
Solution
To get desired result make Table-Valued Functions with CTE Query and return result in a table for a specific category based on the parameter(s).
(
@CategoryID int
)
RETURNS @table_childs TABLE (CategoryID int)
AS
BEGIN
WITH CTE(CategoryID, ParentCategoryID, CategoryName)
AS (SELECT p2.CategoryID, p2.ParentCategoryID, p2.CategoryName
from tbl_Category p2
where
p2.CategoryID = @CategoryID
UNION ALL
SELECT PC.CategoryID, pc.ParentCategoryID, pc.CategoryName
from tbl_Category PC
Inner join CTE on CTE.CategoryID = PC.ParentCategoryID
)
insert into @table_childs
SELECT distinct CategoryID
FROM CTE AS CTE_1
RETURN
END
This CTE query runs recursive and will return entire hierarchy of the required category given in parameter.
Now you can use this result set in WHERE CLAUSE of any query you need
WHERE CategoryID in(
SELECT CategoryID
FROM fnc_GetChildCategories(1))
UPDATE tbl_Category SET isArchived = 1
WHERE CategoryID in(
SELECT CategoryID
FROM fnc_GetChildCategories(1))