title: TSQL Select Statement using Case or Join
body: <p>I am a little stuck on a situation that I have been trying to fight through. I have a page that allows a user to select all the filter options they want to search by and then it runs the query on that data.</p>
  
  <p>Every field requires something to be picked but on a new field I am introducing, it's going to be optional.</p>
  
  <p>It allows you to provide a list of supervisors and it will then provide all records where the agents supervisor is in the list provided; pretty straight forward. However, I am trying to make this optional as I don't want to always search by users. If I don't provide a name in the UI to pass to the stored procedure, then I want to ignore this part of the statement and get me everything regardless of the manager.</p>
  
  <p>Here is the query I am working with: </p>
  
  <pre><code>SELECT a.[escID],
             a.[escReasonID],
             b.[ArchibusLocationName],
             c.[ArchibusLocationName],
             b.[DepartmentDesc],
             c.[DepartmentDesc],
             a.[escCreatedBy],
             a.[escWorkedBy],
             a.[escNotes],
             a.[preventable],
             a.[escalationCreated],
             a.[escalationTracked],
             a.[feedbackID],
             typ.[EscalationType],
             typ.[EscalationTypeText] AS escalationType,
             d.reasonText AS reasonText
      FROM   [red].[dbo].[TFS_Escalations] AS a
             LEFT OUTER JOIN
             red.dbo.EmployeeTable AS b
             ON a.escCreatedBy = b.QID
             LEFT OUTER JOIN
             red.dbo.EmployeeTable AS c
             ON a.escWorkedBy = c.QID
             LEFT OUTER JOIN
             red.dbo.TFS_Escalation_Reasons AS d
             ON a.escReasonID = d.ReasonID
             INNER JOIN
             dbo.TFS_EscalationTypes AS typ
             ON d.escType = typ.EscalationType
      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 leaderQID FROM @sourceLeaders)
  </code></pre>
  
  <p>The part that I am trying to make option is the very last line of the query:</p>
  
  <pre><code>AND b.MgrQID IN (SELECT leaderQID FROM @sourceLeaders)
  </code></pre>
  
  <p>Essentially, if there is no data in the temp table <code>@sourceLeaders</code> then it should ignore that piece of the query.</p>
  
  <p>In all of the other instances of the <code>WHERE</code> clause, something is always required for those fields which is why that all works fine. I just cant figure out the best way to make this piece optional depending on if the temp table has data in it (the temp table is populated by the names entered in the UI that a user COULD search by).</p>