SQL - Different WHERE clause dependant upon variable

Viewed 503

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?

1 Answers
Related