title: SQL CASE switch for parameter
body: <p>I have a parameter that can be 1 of 3 possible values 0, 2 or NULL (these are values for an enum 0 is Pending, 1 is UnderReview and 2 is Closed). If it is NULL it should retrieve all regardless of the Status but If it is 2 it should retrieve all the closed ones however if the value is 0 it should retrieve the ones that are pending or under review. This last one means that I want to retrieve records that have a status of either 0 or 1. I have the following code so far and tried with a CASE switch but it doesn't work.</p>
  
  <pre><code>SELECT * FROM PurchaseOrder
      WHERE (CreationDate &gt;= @StartDate AND CreationDate &lt;= @EndDate)
      AND (@POStatus IS NULL OR [Status] = @POStatus)
      AND (@POStatus IS NULL OR [Status] = CASE @POStatus WHEN 0 THEN 1 END)
      AND (@PurchaseOrderIdSearch IS NULL OR PurchaseOrderId LIKE @PurchaseOrderIdSearch)
      AND EmployeeId = @EmployeeId
      ORDER BY CreationDate DESC, PurchaseOrderId DESC
  </code></pre>
  
  <p>I solved it adding an if statement, I was looking for a simpler way to do it to avoid repetition, this is how it looks like now:</p>
  
  <pre><code>IF @POStatus = 0
      BEGIN
          SELECT * FROM PurchaseOrder
          WHERE (CreationDate &gt;= @StartDate AND CreationDate &lt;= @EndDate)
          AND (([Status] = @POStatus)
          OR ([Status] = 1))
          AND (@PurchaseOrderIdSearch IS NULL OR PurchaseOrderId LIKE @PurchaseOrderIdSearch)
          AND EmployeeId = @EmployeeId
          ORDER BY CreationDate DESC, PurchaseOrderId DESC
      END
      ELSE
      BEGIN
          SELECT * FROM PurchaseOrder
          WHERE (CreationDate &gt;= @StartDate AND CreationDate &lt;= @EndDate)
          AND (@POStatus IS NULL OR [Status] = @POStatus)
          AND (@PurchaseOrderIdSearch IS NULL OR PurchaseOrderId LIKE @PurchaseOrderIdSearch)
          AND EmployeeId = @EmployeeId
          ORDER BY CreationDate DESC, PurchaseOrderId DESC
      END
  </code></pre>