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