How to Handle 'Optional' Where Clause Filters in Sql

Optional Arguments in WHERE Clause

Alternatively to the ISNULL / COALESCE options, you can test the parameters for being null:

SELECT NAME  
FROM TABLE  
WHERE  
    (@City IS NULL OR City = @City)
AND 
    (@Gender IS NULL OR Gender = @Gender)
AND 
    (@Age IS NULL OR Age = @Age) 

TSQL Optional Where Clause

By looking at "If there is a value in the filter, it needs to look up submissions just on those values category=3 department=5. However, when there those are empty on the filter settings, it needs to ignore the category and department and get me the record regardless of what the value is."

So let say you have filter value in parameter called @Filter you can do something like below

Where (@Filter is null OR (@Filter is not null and category=3 and department=5))

Update

Didn't notice you have two parameters

Where (@category is null or (category = @category))
      and (@department is null or (department= @department))

Filtering conditional in WHERE clause

It sounds like you're describing, what is commonly referred to as an optional parameter.
If the user enters a parameter value, they want to filter based on that, if not, ignore it altogether.

It would typically look something like this:

DECLARE @PEN_TIPO INT;

(A.PEN_TIPO = @PEN_TIPO OR @PEN_TIPO IS NULL)
OPTION(RECOMPILE);

Please note that I added "OPTION(RECOMPILE)" to the end of the query.
You'll want to add this to you query too, so that the optimizer can create an optimized plan based on the chosen parameter value.

Stored Procedure with optional WHERE parameters

One of the easiest ways to accomplish this:

SELECT * FROM table 
WHERE ((@status_id is null) or (status_id = @status_id))
and ((@date is null) or ([date] = @date))
and ((@other_parameter is null) or (other_parameter = @other_parameter))

etc.
This completely eliminates dynamic sql and allows you to search on one or more fields. By eliminating dynamic sql you remove yet another security concern regarding sql injection.

How to handle optional parameters in SQL query?

Yes, using any of the following:

WHERE m.id_pk = NVL(n_RequiredId, m.id_pk);
WHERE m.id_pk = COALESCE(n_RequiredId, m.id_pk);
WHERE (n_RequiredId IS NULL OR m.id_pk = n_RequiredId);

...are not sargable. They will work, but perform the worst of the available options.

If you only have one parameter, the IF/ELSE and separate, tailored statements are a better alternative.

The next option after that is dynamic SQL. But coding dynamic SQL is useless if you carry over the non-sargable predicates in the first example. Dynamic SQL allows you to tailor the query while accommodating numerous paths. But it also risks SQL injection, so it should be performed behind parameterized queries (preferably within stored procedures/functions in packages.

---

Related Topics