Apply conditional filtering in WHERE clause

Question

I am joining a few tables in the SELECT statement like below and it has three parameters.

DECLARE @Jobid      INT=0,
        @leadid     INT=0,
        @employeeid INT=0

SELECT e.id,
       l.id,
       j.id,
       e.NAME,
       l.NAME,
       j.NAME
FROM   employee e
       INNER JOIN leads l
               ON e.leadid = l.id
       INNER JOIN Jobs j
               ON j.id = e.Jobid

This works fine without filtering.

In the WHERE clause I have to add something like below. If any of the three ID's is greater than zero then I have to consider the filter in the WHERE clause; if it is equal to zero I won't consider that particular condition.

If @jobid> 0
then introduce this condition in where clause (j.id=@jobid) 

If @leadid> 0
then introduce this condition in where clause (l.id=@leadid)

If @employeeid> 0
then introduce this condition in where clause (e.id=@employeeid)

I know how to achieve this through dynamic SQL but I need a static SQL statement to achieve this.

I tried the following:

where 
((J.Id = @Jobid and @Jobid>0 )
or  @Jobid=0)
and (
(L.Id = @leadid and @leadid>0 )
or  @leadid=0
)
and (
(e.Id = @employeeid and @employeeid >0 )
or  @employeeid =0
)

But there is a performance hit.

Kindly suggest me any other better way of doing this in static SQL especially using Case When.

Check this [answer](http://stackoverflow.com/questions/34730633/how-to-add-to-where-clause-depending-on-parameter-value/34730678#34730678). — Mihail Stancescu, Jan 21 '16 at 10:07
Simply `(J.Id = @Jobid or @Jobid=0)` etc will do the same. (Don't know if it will be any faster though...) — jarlh, Jan 21 '16 at 10:11

Zohar Peled · Answer 1 · 2016-01-21T10:52:08.933

First, this ((J.Id = @Jobid and @Jobid>0) or @Jobid=0) can be replaced
with this (@Jobid = 0 or J.Id = @Jobid). Note that since 0 is obviously not a valid value for job id (or employee, or lead), the and part is irrelevant since no record will ever contain an id of 0.

Second, don't use 0 as an invalid value, use null instead. it's not going to effect performance, but it's a better programming habit, since 0 might very well be a valid value in other situations.

Third, catch-all queries are known to suffer performance hit, especially in stored procedures, since the cached execution plan might not be the best for the current execution. To the best of my knowledge, the best way to handle this is to add a recompile hint to the query, as suggested in this article and in that article.

So, I suggest your query to look like this:

CREATE PROCEDURE <procedure name>
(
        @Jobid      INT=NULL,
        @leadid     INT=NULL,
        @employeeid INT=NULL
)
AS

SELECT e.id,
       l.id,
       j.id,
       e.NAME,
       l.NAME,
       j.NAME
FROM   employee e
       INNER JOIN leads l
               ON e.leadid = l.id
       INNER JOIN Jobs j
               ON j.id = e.Jobid 
WHERE (@Jobid IS NULL OR J.Id = @Jobid)
AND (@leadid IS NULL OR l.Id = @leadid)
AND (@employeeid IS NULL OR e.Id = @employeeid)
OPTION(RECOMPILE)

GO

select performance are usually improved with correct indexing of the tables. However, Indexing correctly requires knowledge not all developers have. It's a subject well worth reading about. I would start here.

Thank you so much for your suggestion. I already selected destination-data answer. Anyhow I will vote up. — StackUser, Jan 21 '16 at 10:32

David Rushton · Accepted Answer · 2016-01-21T10:29:46.077

0

You can use a CASE expressions like this:

CASE Example

WHERE
    CASE 
        WHEN @Jobid > 0 THEN @Jobid     -- When @Jobid supplied use it.
        ELSE J.id                       -- When not; return current value.
    END = J.id

When @Jiobid exceeds 0 it is compared to J.id. When not J.id is compared to itself, which will of course always result in a match.

Personally I prefer the approach suggested by @jarlh in the comments above. The simplicity makes the code easier to follow.

@jarlh Example

WHERE
    (J.Id = @Jobid or @Jobid=0)

If these approaches don't improve your performance try adding your schema and some sample records to the question. You might also consider posting the execution plan.

edited Jan 21 '16 at 10:29

answered Jan 21 '16 at 10:22

David Rushton

4,915
1
17
31

You should also look at the answer from @ZoharPeled. He/She makes a good point on the use of 0. – David Rushton Jan 21 '16 at 10:33
One thing about this answer - in a case when the target column (J.Id, in this case) is nullable, it might lead to incorrect results since null = null will evaluate to false. Other then that, I saw no change in performance when testing the case...when version, so it's a question on what's more readable in the author's opinion. – Zohar Peled Jan 21 '16 at 10:51

Apply conditional filtering in WHERE clause

2 Answers2

Linked

Related