title: Using CASE within a where clause - with a parameter
body: <p>I am looking for a good example of a case statement inside a where clause, using a variable.  The number returned by the variable determines the result of the where clause.  My query looks like this:</p>
  
  <pre><code>IF          @RequestType = 99
      SELECT  @Received = count(*)
      FROM    Request r
      WHERE   request_received_date between @beginDate and @endDate
      AND     request_type_id &lt;&gt; 5
      ELSE IF @RequestType = 100
  BEGIN
      SELECT  @Received = count(*)
      FROM    Request r
      WHERE   request_received_date between @beginDate and @endDate
  END
      ELSE
  BEGIN
      SELECT  @Received = count(*)
      FROM    Request r
      WHERE   request_received_date between @beginDate and @endDate
      AND     request_type_id = 
              (CASE WHEN @RequestType = 1 THEN (1)
              WHEN @RequestType = 2 THEN (2)
              WHEN @RequestType = 3 THEN (3)
              WHEN @RequestType = 4 THEN (4)
              WHEN @RequestType = 5 THEN (5)
              END)
  END
  </code></pre>
  
  <p>So as you can see, there are 7 options - the customer wants to be flexible with the amount of data they see in the report.  This looks cumbersome to me but I can't think of any other way to do this.</p>