title: T-SQL - Using CASE with Parameters in WHERE clause
body: <p>I'm running a report on a <code>Sales</code> table:</p>
  
  <p><code>SaleId INT | SalesUserID INT | SiteID INT | BrandId INT| SaleDate DATETIME</code></p>
  
  <p>I'm having a nightmare trying to do something like this with a set of Nullable parameters <code>@SalesUserID</code>, <code>@SiteId</code>, <code>@BrandID</code> and two DateTime params.</p>
  
  <p>Additional Point: Only ONE of the filter parameters will ever be passed as a Non-Null value.</p>
  
  <pre><code>SELECT * from Sales
  WHERE
       SaleDate BETWEEN @StartDate AND @EndDate
  
  AND
      SalesUserID IN
  (
      Select SalesUserID FROM Sales
      WHERE
          SaleDate BETWEEN @StartDate AND @EndDate
  
      AND
  
          CASE
              WHEN @SalesUserId IS NOT NULL THEN SalesUserId = @SalesUserID
              WHEN @SiteId Is Not Null THEN SiteId = @SiteId
              ELSE BrandId = @BrandID
          END
  
  )
  </code></pre>
  
  <p>My use of <code>CASE</code> here smells bad but I'm not sure how to correct it. Can you please assist?</p>
  
  <p>Thanks.</p>
  
  <p>5arx</p>