Apply conditional filtering in WHERE clause
<p>I am joining a few tables in the SELECT statement like below and it has three parameters.</p>

<pre><code>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 
</code></pre>

<p>This works fine without filtering.</p>

<p>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.</p>

<pre><code>If @jobid&gt; 0
then introduce this condition in where clause (j.id=@jobid) 

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

If @employeeid&gt; 0
then introduce this condition in where clause (e.id=@employeeid)
</code></pre>

<p>I know how to achieve this through dynamic SQL but I need a static SQL statement to achieve this.</p>

<p>I tried the following:</p>

<pre><code>where 
((J.Id = @Jobid and @Jobid&gt;0 )
or  @Jobid=0)
and (
(L.Id = @leadid and @leadid&gt;0 )
or  @leadid=0
)
and (
(e.Id = @employeeid and @employeeid &gt;0 )
or  @employeeid =0
)
</code></pre>

<p>But there is a performance hit.</p>

<p>Kindly suggest me any other better way of doing this in static SQL especially using <code>Case When</code>.</p>