SQL Switch/Case in SQL Server 2008 R2

Question

Here is my test T-SQL.

DECLARE @TestVal INT

SET @TestVal = 1

SELECT 
   CASE @TestVal
      WHEN (1 | 2 | 6) THEN 'First' // I would like to check 1 or 2 or 6.
      WHEN 3 THEN 'Second'
      WHEN 4 THEN 'Third'
      ELSE 'Other'
   END

The current result is 'Other'.

I would like to get the result as 'First'. How can I use (OR statement) in my T-SQL.

Best regards

possible duplicate of [SQL Server: CASE WHEN OR THEN ELSE END => the OR is not supported](http://stackoverflow.com/questions/5487892/sql-server-case-when-or-then-else-end-the-or-is-not-supported) — Cᴏʀʏ, Apr 14 '15 at 02:09

score 6 · Accepted Answer · answered Apr 14 '15 at 02:08

6

Use the condition form of case:

SELECT (CASE WHEN @TestVal IN (1, 2, 6) THEN 'First' 
             WHEN @TestVal = 3 THEN 'Second'
             WHEN @TestVal = 4 THEN 'Third'
             ELSE 'Other'
        END)

answered Apr 14 '15 at 02:08

Gordon Linoff

1,198,228
53
572
709

score 2 · Answer 2 · answered Apr 14 '15 at 02:08

2

Try this instead:

SELECT
    CASE WHEN @TestVal IN (1, 2, 6) THEN 'First'
         WHEN @TestVal = 3 THEN 'Second'
         WHEN @TestVal = 4 THEN 'Third'
         ELSE 'Other'
    END

answered Apr 14 '15 at 02:08

Cᴏʀʏ

101,556
20
162
188

score 2 · Answer 3 · answered Apr 14 '15 at 12:32

Above answers are correct also you can use OR like this:

DECLARE @TestVal INT

SET @TestVal = 2

SELECT (CASE WHEN (@TestVal = 1 OR @TestVal=2 OR @TestVal=6) THEN 'First' 
             WHEN @TestVal = 3 THEN 'Second'
             WHEN @TestVal = 4 THEN 'Third'
             ELSE 'Other'
        END)

SQL Switch/Case in SQL Server 2008 R2

3 Answers3