Where is the condition depending on the value of the variable

Question

I have 2 fields that I want to send to the values ​​inside the instruction WHERE.

• If variable = 0, set 2 field values ​​to 100.
• If the variable = 1, then set the same values ​​for the field to 101.

In my imaginary world, somehow it 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 now I'm here.

Answer 1

If I understand 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 can rewrite your CASE conditions in the form of a join, as shown 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 ...

, OR , , ( ) . ( .)

Answer 3

You can also just do

WHERE od.StatusCd = 100 + @ReportType AND odm.StatusCd = 100 + @ReportType

Or you can declare a new variable with a value in:

DECLARE @StatusCd = 100 + @ReportType

Keep it simple.