mssql: case statements with multiple columns

SQL Apprentice Question
A standard case statement in MSSQL server looks like:

...WHERE s.idMarket =
CASE @Mode
WHEN 'Sales' THEN 1
ELSE 2
END


but I want a similar statement to use multiple values of 'idMarket' for
each value of '@Mode'


e.g.


...WHERE s.idMarket =
CASE @Mode
WHEN 'Sales' THEN 1, 3, 5, 6
ELSE 2, 4, 7, 8, 9
END


is this possible through an mssql query?

Celko Answers
>> A standard case statement in MSSQL server looks like: <<


There is no CASE statement in SQL; there is a CASE expression. An
expression returns a scalar value of a known data type.


>> I want a similar statement [sic] to use multiple values [sic] of 'idMarket' for each value of '@Mode' <<


Again, you missed the concept "expression" and "scalar" so the
question is wrong. Then even if it would have worked, you would have
to use IN() and not = in the predicate. Besides not bothering to learn
SQL, why did you violate ISO-11179 by putting "id-" as a prefix? Why
did you use a vague nmae like "mode" --mode of what??

Try something like this:


...WHERE CASE
WHEN S.market_id IN (1, 3, 5, 6)
AND @mode = 'sales'
THEN 'T'
WHEN S.market_id IN (2, 4, 7, 8, 9)
AND @mode <> 'sales'--mode of what??
THEN 'T' ELSE 'F' END = 'T';