Nested CASE Statement With ELSe (SQL Server)

sql, sql-server-2008

Solution

First, you don't need to nest `case` statements. Just use one case:

select (CASE WHEN A IS NULL AND B IN ('C', 'D') THEN NULL
             WHEN A IS NULL AND X NOT IN ('C','D') THEN Z
             WHEN A IS NOT NULL THEN SOMETHING_ELSE
        END) as Result

Note that when `A IS NULL` but the first two conditions are not met, then the return value will be `NULL`.

Because `case` statements are evaluated sequentially, this is simpler to write as:

select (CASE WHEN A IS NOT NULL THEN SOMETHING_ELSE
             WHEN B IN ('C', 'D') THEN NULL
             WHEN X NOT IN ('C', 'D') THEN Z
        END) as Result

The first condition captures when `A` is not `NULL`. Hence the second two are when `A` is `NULL`.

Problem

I have a CASE statement something like following ``` CASE WHEN A IS NULL THEN CASE WHEN B IN ('C','D') THEN NULL WHEN X NOT IN ('C','D') THEN Z End ELSE SOMETHING_ELSE -- Want to get here When 'A' IS NOT NULL END AS 'Result' ``` I want to get to ELSE part when First CASE is not true i-e 'A' is not NULL. Can anyone suggest if i have wrongly nested them? not getting results right. Many Thanks,

Original source