2

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?

  • 1
    The bloat is only in the wording (SQL is a bloated language, a lot of people would agree). You can move the then 1 else 0 end phrase to a single line instead of three to make it look more elegant. What you propose could be done if SQL-Server had a boolean datatype - but it hasn't. – ypercubeᵀᴹ Aug 27 '14 at 10:20
  • True, I can push it all on one line, but it feels redundant, akin to writing if (a & b & c) { d = true; } else { d = false; } in a programming language compared to just d = (a & b & c); (I know comparing SQL to programming languages is a suspect practice at best, but this seems fairly baseline stuff). I wonder why SQL Server lacks a boolean data type? – Kai Aug 27 '14 at 10:34
  • what about BIT datatype ? – DamagedGoods Aug 27 '14 at 10:58
  • I guess it's a matter of taste, but to me the original query is far better readable then the version you suggest. – Rene Aug 27 '14 at 13:25
5

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

1

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.

0

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.

  • I wrote my answer then realised that the built-in function is only available from 2012+. I've left my answer encase it's helpful to others and noted that restriction in the answer – AeroX Aug 27 '14 at 15:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.