Multiple WHEN inside without CASE expressions 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 could not find an example where there was no expression, and it had more than one WHEN, so I wonder if this is normal in this case:

    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

The case takes the following form

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

Edit

This suggests that the use of your other Microsoft SQL Server DBMS may vary.

Answer 2

Yes, that’s fine, but I would align "WHEN" 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

, (select...) , .

Answer 3

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

For nesting case statements, this can also be done (up to 10 nested case statements allowed in sql)

http://msdn.microsoft.com/en-us/library/ms181765.aspx