Named query with many conditional WHERE clauses without query string?

Ignition
23

It has been a little while since I’ve implemented something like this, so my apologies in advance if the syntax isn’t quite right… What I would do for optional filters is leverage short-circuit evaluation for the parameters and feed None/NULL when the dropdown is set to All. The query would then look something like this:

SELECT column_a, column_b FROM table
WHERE 1=1
  AND (@for_filter IS NULL OR for_column=@for_filter)
  AND (@rsvp_filter IS NULL OR rsvp_column=@rsvp_filter)
  AND ...

This general method lets you bundle in all of the filters (and not have to maintain separate queries) and automatically includes rows for a given where clause where the filter parameter is NULL.

Hope this helps…

3 Likes