Multiple WHEN inside no-expression CASE in SQL?

Question

    DECLARE @TestVal int
SET @TestVal = 5

SELECT
    CASE
     WHEN @TestVal <=3 THEN 'Top 3'
     ELSE 'Other'
    END

I saw this sample code online but I couldn't find an example where there was no expression and it had more than one WHEN, so I am wondering if this type of thing is OK:

    DECLARE @TestVal int
SET @TestVal = 5

SELECT
    CASE
     WHEN @TestVal <=3 THEN 'Top 3'
                WHEN (select ...) = 1 THEN 'Other Value'
                WHEN (select ...) = 2 THEN 'Other Value 2'
     ELSE 'Other'
    END

Or do I need to say CASE WHEN for each line?

Answer 1

It should be OK - depending on what your DBMS supports. Why don't you try it?

Answer 2

Case takes the following form

CASE WHEN Condition THEN Result
     WHEN Condition2 THEN Result2
ELSE Default
END

Edit

This assumes your using Microsoft SQL Server other DBMS might be different

Answer 3

Yes, that's fine, but I would line up the "WHEN"s vertically and explain it more like this:

SELECT
    CASE
        WHEN @TestVal <=3  THEN 'Top 3'
        WHEN @TestVal <=10 THEN 'Top 10'
        WHEN @TestVAl <=25 THEN 'Top 25'
        ELSE 'Other'
    END

The formatting might just be a markdown glitch, but the (select...) in your example complicated what should be a simpler snippet.