How can I make this WHERE clause more readable?

Question

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

Answer 1

Add comments, they're the usual way of making complicated source code easier to understand.

There's not a lot to add in your case because the meaning of the code is simple even if the implementation isn't easy to take in at a glance, but adding a few short comments may make it much more readable, especially for someone who's reading through the whole thing trying to understand the flow of logic. I would just do this:

where
    -- optional category
    (@Category = 'all' or
    (@Category = 'omitted' and Category is null) or
    (@Category = Category and (
        -- optional subcategory
        @SubCategory = 'all' or
        (@SubCategory is null and SubCategory is null) or
        (@SubCategory = SubCategory and (
            -- optional subsubcategory
            @SubSubCategory = 'all' or
            (@SubSubCategory is null and SubSubCategory is null) or
            @SubSubCategory = SubSubCategory
        ))
    ))
)

Now I can quickly see the purpose of the code, and even if the detailed logic is awkward to follow it may not matter at all because I may not need to change it, just understand what it's doing. If I do need to modify it, I'll have no choice but to work through it in detail anyway, in which case the comments will help to point me in the right direction.

You didn't mention if you're using SQL Server or Sybase, but if it's SQL Server and if you haven't read it already, this article is well worth reading. I wouldn't be too dogmatic about avoiding dynamic SQL, it certainly has its issues but it has its uses too.

Answer 2

This kind of condition may actually hurt performance quite a lot, because query optimizer most likely will be confused by complex filtering condition and would have to do a table scan rather than index seek (assuming that you have index over Category and SubCategory). In order to improve querying performance you should simplify WHERE clause.

Best solution would be to utilize client-side ORM capabilities to generate dynamic filtering based on available data. If that isn't possible, then I would go with one of the following approaches (I would prefer Multiple queries approach in case when select statement is simple):

• Dynamic sql:

  declare @sql nvarchar(max);
  set @sql = 'select [field_list] from table_name ' + case 
      when @Category = 'all' then ''
      when @Category = 'omitted' then 'where Category is null'
      else 'where Category = @Category' + case
          when @SubCategory = 'all' then ''
          when @SubCategory is null then ' and SubCategory is null'
          else ' and SubCategory = @SubCategory' + case
              when @SubSubCategory = 'all' then ''
              when @SubSubCategory is null then ' and SubSubCategory is null'
              else ' and SubSubCategory = @SubSubCategory'
          end
      end
  end
  
  exec sp_executesql @stmt = @sql,
      @params = N'@Category varchar(100), @SubCategory varchar(100), @SubSubCategory varchar(100)',
      @Category = @Category,
      @SubCategory = @SubCategory,
      @SubSubCategory = @SubSubCategory;
  

• Multiple queries approach:

  if (@Category = 'all')
      select [field_list]
      from table_name
  else if (@Category = 'omitted')
      select [field_list]
      from table_name
      where Category is null
  else if (@SubCategory = 'all')
      select [field_list]
      from table_name
      where Category = @Category
  else if (@SubCategory is null)
      select [field_list]
      from table_name
      where Category = @Category and SubCategory is null
  else if (@SubSubCategory = 'all')
      select [field_list]
      from table_name
      where Category = @Category and SubCategory = @SubCategory
  else if (@SubSubCategory is null)
      select [field_list]
      from table_name
      where Category = @Category and SubCategory = @SubCategory and SubSubCategory is null
  else
      select [field_list]
      from table_name
      where Category = @Category and SubCategory = @SubCategory and SubSubCategory = @SubSubCategory