Using expressions in the SELECT list to thin down a CASE statement

Question

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?

Answer 1

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 |.

Answer 2

Unfortunately you are not going to really get around SQL's verbosity here.

You could write a function (of family of functions) to reduce the amount of text in the query, so you have something like:

select
    Column1 = dbo.YeyOrNay(Something, 2, SomethingElse, 1, AnotherThing, 1, 2),
    Column2 = dbo.YeyOrNay(Something, 3, SomethingElse, 0, AnotherThing, 2, 3),
    ...

or

select
    Column1 = dbo.Col1Func(Something, SomethingElse, AnotherThing),
    Column2 = dbo.col2Func(Something, SomethingElse, AnotherThing),
...

but while that makes the SELECT neater you might be reducing the clarity (and efficiency, though if you write the functions carefully the query planner will unroll them to reduce this effect) of the code more generally.

Answer 3

If you're trying to make your lines shorter then you could use IIF( ..., 1, 0 ) instead of CASE ... THEN 1 ELSE 0 END.

SELECT
    Column1 = IIF(@Something = 2 AND @SomethingElse IS     NULL AND @AnotherThing IN (1,2), 1, 0)
    Column2 = IIF(@Something = 3 AND @SomethingElse IS NOT NULL AND @AnotherThing IN (2,3), 1, 0)
;

Note: Only available from SQL Server 2012 onwards.