Without changing the semantics and performance, how can I make this where clause more readable?
where
(@Category = 'all' or
(@Category = 'omitted' and Category is null) or
(@Category = Category and (
@SubCategory = 'all' or
(@SubCategory is null and SubCategory is null) or
(@SubCategory = SubCategory and (
@SubSubCategory = 'all' or
(@SubSubCategory is null and SubSubCategory is null) or
@SubSubCategory = SubSubCategory
))
))
)
At a high level, this clause represents a user query where the user has optionally specified a category, subcategory, and sub-subcategory on a table. For compatibility reasons, I cannot change any semantics (e.g., I cannot improve consistency by using @Category is null
instead of @Category = 'omitted'
)