CASE IN statement with multiple values

Question

Is there a way to make a CASE statement with an IN clause   SELECT CASE c Number IN   1121231   31242323   THEN 1 IN   234523   2342423   THEN 2 END AS Test FROM tblClient c

User · Answer

You can return the same value from several matches:

SELECT
  CASE c.Number
    WHEN '1121231' THEN 1
    WHEN '31242323' THEN 1
    WHEN '234523' THEN 2
    WHEN '2342423' THEN 2
  END AS Test
FROM tblClient c

This will probably result in the same execution plan as Martins suggestion, so it's more a matter of how you want to write it.

User · Answer

If you have more numbers or if you intend to add new test numbers for CASE then you can use a more flexible approach   DECLARE  Numbers TABLE       Number VARCHAR 50  PRIMARY KEY      Class TINYINT NOT NULL    INSERT  Numbers VALUES   1121231  1   INSERT  Numbers VALUES   31242323  1   INSERT  Numbers VALUES   234523  2   INSERT  Numbers VALUES   2342423  2    SELECT c    n Class FROM   tblClient c   LEFT OUTER JOIN    Numbers n ON c Number   n Number    Also  instead of table variable you can use a regular table

User · Answer

The question is specific to SQL Server  but I would like to extend Martin Smith s answer    SQL 2003 standard allows to define multiple values for simple case expression   SELECT CASE c Number           WHEN  1121231   31242323  THEN 1           WHEN  234523   2342423  THEN 2        END AS Test FROM tblClient c    It is optional feature  Comma-separated predicates in simple CASE expression     F263    Syntax   CASE  lt common operand gt       WHEN  lt expression gt     lt expression gt       THEN  lt result gt       WHEN  lt expression gt     lt expression gt       THEN  lt result gt                 ELSE  lt result gt   END   As for know I am not aware of any RDBMS that actually supports that syntax

User · Answer

Yes  You need to use the  Searched  form rather than the  Simple  form of the CASE expression  SELECT CASE          WHEN c Number IN    1121231    31242323    THEN 1          WHEN c Number IN    234523    2342423    THEN 2        END AS Test FROM   tblClient c

[sql] CASE IN statement with multiple values

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005