title: TSQL WHERE Clause based on Temp Table
body: <p>I have a large query that I am using to pull data for a report based on a filter form a user fills out. They can choose things such as location(s), department(s) as well as supervisors.</p>
  
  <p>What I am currently doing is if they select <code>All</code> as the option instead of choosing a specific location, I have a temp table of all the locations that I do a <code>WHERE IN()</code> clause on. </p>
  
  <p>I am trying to do something similar for my supervisor search options. A user doesn't have to select a supervisor to search by and in that case, I need it to ignore that <code>AND</code> part of the <code>WHERE</code> clause.</p>
  
  <p>The last line in the code below <code>AND b.MgrQID IN (SELECT DISTINCT QID FROM @sourceLeaderEmps)</code> needs to be ignored if the temp table @sourceLeaderEmps contains no records.</p>
  
  <p>Is there an easy way to go about doing this?</p>
  
  <pre><code>WHERE  B.[ArchibusLocationName] IN (SELECT location
                                          FROM   @tmLocations)
             AND C.[ArchibusLocationName] IN (SELECT location
                                              FROM   @subLocations)
             AND B.[DepartmentDesc] IN (SELECT department
                                        FROM   @tmDepartments)
             AND C.[DepartmentDesc] IN (SELECT department
                                        FROM   @subDepartments)
             AND DATEDIFF(second, '19700101', CAST (CONVERT (DATETIME, A.[escalationCreated], 121) AS INT)) &gt;= @startDate
             AND DATEDIFF(second, '19700101', CAST (CONVERT (DATETIME, A.[escalationCreated], 121) AS INT)) &lt;= @endDate
             AND a.[PREVENTABLE] IN (SELECT PREVENTABLE
                                     FROM   @preventable)
             AND b.MgrQID IN (SELECT DISTINCT QID FROM @sourceLeaderEmps) 
  </code></pre>