Use CTE query in WHERE clause

categorytable2

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).

CREATE FUNCTION [dbo].[fnc_GetChildCategories]
(
@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

SELECT CategoryName from tbl_category
WHERE CategoryID in(
SELECT CategoryID
FROM fnc_GetChildCategories(1))

UPDATE tbl_Category SET isArchived = 1
WHERE CategoryID in(
SELECT CategoryID
FROM fnc_GetChildCategories(1))

qryResult

  

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>