SQL Server – Using Expressions in SELECT to Simplify CASE Statements

caseselectsql serversql-server-2008-r2

I have a query I'm working through rewriting – it has a huge bunch of case statements in the select list that all basically look like this:

select
    Column1 = 
        case when (Something = 2) and (SomethingElse is null) and (AnotherThing in (1, 2))
        then 1 
        else 0
        end,
    Column2 = 
        case when (Something = 3) and (SomethingElse is not null) and (AnotherThing in (2, 3))
        then 1 
        else 0
        end,
    ...

With particular note to the "when <expressions> then 1 else 0" logic common across all of them.

It seems really bloated and wordy, a heavy-handed use of the case statement – my programming brain is telling me this could be achieved much more succinctly with some basic binary &ing logic, removing the case entirely:

select
    Column1 = (Something = 2) & (SomethingElse is null) & (AnotherThing in (1, 2)),
    Column2 = (Something = 3) & (SomethingElse is not null) & (AnotherThing in (2, 3)),
    ...

Unfortunately whilst SQL Server is able to handle something like select 1 & 0 (returns 0), it seems to choke on parsing expressions in the select list (Incorrect syntax near '=') – is it possible to do something like this? Some kind of evaluation function, maybe?

Best Answer

T-SQL doesn't support Booleans like that. But you could use CROSS APPLY with a subquery that uses CASE to produce a bunch of 1s and 0s, and then combine them in the SELECT clause using the bitwise operators & and |.

Related Question