I'm using a variable to get my SELECT to run in 2 different "modes":
DECLARE @Mode as Varchar(1) = 'A'
SELECT
CASE
WHEN @Mode = 'A'
THEN FieldABC * 2
ELSE FieldABC * 3
END AS FieldABC,
CASE
WHEN @Mode = 'A'
THEN FieldDEF
ELSE FieldGHI
END AS FieldJKL
WHERE
FieldABC BETWEEN 0 AND 100
When I want to run the "B" version of the SELECT, I just have to change the variable value. This all works fine.
I now want to change the WHERE clause so it too is dependent upon the variable:
IF @Mode = 'A' THEN
WHERE FieldABC > 0
ELSE
WHERE FieldABC < 0
END IF
Except, this syntax is totally nonsense in SQL!
How can I achieve the 2 different flavours of my WHERE clause dependent upon the same variable - or some other simple way of switching the SELECT between the 2 different modes?