SQL 'CASE WHEN x' vs. 'CASE x WHEN' with greater-than condition?

Question

This is a questions about the two ways to use the SELECT CASE in MS SQL [CASE WHEN X = Y] and [CASE X WHEN Y]

I am trying to define buckets for a field based on its values. I would need to use ranges, so it is necessary to use the "<" or ">" identifiers.

As a simple example, I know it works like this:

SELECT CASE WHEN x < 0 THEN 'a' WHEN X > 100 THEN 'b' ELSE 'c' END

Now I have to write a lot of these, there will be more than 3 buckets and the field names are quite long, so this becomes very difficult to keep clean and easy to follow. I was hoping to use the other way of the select command but to me it looks like I can only use it with equals:

SELECT CASE X WHEN 0 then 'y' ELSE 'z' END

But how can I use this form to specify range conditions just as above? Something like:

SELECT CASE X WHEN < 0 THEN 'a' WHEN > 100 THEN 'b' ELSE "c" END

This one does not work.

Thank You!

Answer 1

As an alternative approach, remember that it is possible to do math on the value that is the input to the "simple" CASE statement. I often use ROUND for this purpose, like this:

SELECT 
  CASE ROUND(X, -2, 1)
    WHEN 0   THEN 'b' -- 0-99
    WHEN 100 THEN 'c' -- 100-199
    ELSE 'a'          -- 200+
  END

Since your example includes both positive and negative open-ended ranges, this approach may not work for you.

Still another approach: if you are only thinking about readability in the SELECT statement, you could write a scalar-valued function to hide all the messiness:

CREATE FUNCTION dbo.ufn_SortValuesIntoBuckets (@inputValue INT) RETURNS VARCHAR(10) AS
BEGIN
    DECLARE @outputValue VARCHAR(10);

    SELECT @outputValue = 
        CASE WHEN @inputValue < 0 THEN 'a'
             WHEN @inputValue BETWEEN 0 AND 100 THEN 'b'
             WHEN @inputValue > 100 THEN 'c'
        END;

    RETURN @outputValue;
END;
GO

So now your SELECT statement is just:

SELECT dbo.ufn_SortValuesIntoBuckets(X); 

One final consideration: I have often found, during benchmark testing, that the "searched" form (which you are trying to avoid) actually has better performance than the "simple" form, depending how many CASEs you have. So if performance is a consideration, it might be worth your while to do a little benchmarking before you change things around too much.

Answer 2

There is no such "third form" of the CASE - only the searched and the simple cases are supported ^*.

You need to use the searched kind (i.e. with separate conditions) even though the variable to which you apply the condition is always the same.

If you are looking to avoid repetition in your SQL when X represents a complex expression, use WITH clause or a nested query to assign a name to the expression that you are selecting.

---

^* The official name of your first example is "searched CASE expression"; your second example is called the "simple CASE expression".