Answer.

In SQL, dynamic filtering is often required — for instance, in reports or searches when filters are defined by the user. The main approaches are:

Dynamic SQL — the query string is built on the fly with conditions for only the non-empty parameters. This is flexible, but attention must be paid to protection against SQL injection (using parameters/escaping).
Conditional filtering via OR — use constructs (@param IS NULL OR field = @param) to ensure that unnecessary conditions do not affect the result if the parameter is not set. This method is safe but can sometimes lead to suboptimal index usage.

Example template for searching orders:

SELECT * FROM Orders
WHERE
    (@CustomerID IS NULL OR CustomerID = @CustomerID)
    AND (@Status IS NULL OR Status = @Status)
    AND (@DateFrom IS NULL OR OrderDate >= @DateFrom)
    AND (@DateTo IS NULL OR OrderDate <= @DateTo);

Trick question.

Question: Why does performance sometimes degrade when using the approach (@param IS NULL OR Column = @param) — even with an index on Column?

Answer: This pattern prevents the optimizer from using the index effectively, as a variable is involved in the condition, causing the query to become a full table scan instead of an index lookup. This is critical with large data volumes.

Example:

WHERE (@name IS NULL OR name = @name)

This condition is not the same as simply name = @name, which does not allow the DBMS to easily use the index.

Story

In the client database search service, "flexible filters" were implemented using the template (@par IS NULL OR Field = @par), and as the volume of orders grew, search speed dropped from seconds to minutes — the indexes stopped working, as the optimizer could not determine a specific value.

Story

In an e-commerce project, filtering was built via dynamic SQL without parameters, directly substituting values. Injections allowed users to retrieve lists of all customers because the filter was vulnerable — passwords had to be urgently changed.

Story

BI reports were generated through a parameterized template without considering that OR conditions eliminate indexed scans. On million-row datasets, reports would "freeze" until the filtering was rewritten to an optimal version with mandatory conditions and separate logic for different combinations of parameters.

How to properly and efficiently implement conditional logic for data filtering in SQL when search parameters are set dynamically (for example, a web filter with any set of conditions)?

Answer.

Trick question.