Where clause depending of the value of a variable

Question

I have 2 fields I want to send values to within a WHERE statement.

• If a variable = 0 then set 2 field values to 100.
• If the variable = 1 then set those same 2 field values to 101.

In my imaginary world, somehow this would work:

Where CASE WHEN @ReportType = 0 THEN 
    od.StatusCd = 100 AND odm.StatusCd = 100
WHEN @ReportType = 1 THEN 
    od.statusCd = 101 AND odm.StatusCd = 101
End
And od.CompletionDate between ....
And so on....

I know this is wrong. But this is where I am at right now.

Answer 1

If I have understood what you are trying to do, this should work :

Where 
(
(@ReportType = 0 AND od.StatusCd = 100 AND odm.StatusCd = 100)
OR
(@ReportType = 1 AND od.statusCd = 101 AND odm.StatusCd = 101)
)
And od.CompletionDate between ....
And so on....

Answer 2

Alternatively you could rewrite your CASE conditions in the form of a join, like below:

...
INNER JOIN
  (
    VALUES (0, 100, 100), (1, 101, 101)
  ) AS v (ReportType, odStatusCd, odmStatusCd)
ON
  @ReportType = v.ReportType
  AND od.statusCd = v.odStatusCd
  AND odm.StatusCd = v.odmStatusCd
WHERE
  od.CompletionDate between ...
  AND ...

Although somewhat less readable, this would avoid using OR and thus might result in a better (more efficient) execution plan. (You would need to test that.)