Boolean Expressions in SQL Select list

Question

I want to create a SQL Select to do a unit test in MS SQL Server 2005. The basic idea is this:

select 'Test Name', foo = 'Result'
from bar
where baz = (some criteria)

The idea being that, if the value of the "foo" column is "Result", then I'd get a value of true/1; if it isn't, I'd get false/0.

Unfortunately, T-SQL doesn't like the expression; it chokes on the equals sign.

Is there some way of evaluating an expression in the SQL select list and getting a returnable result? (Or some other way of achieving the unit testing that I want?)

EDIT: 3 great, answers, all built around CASE. I'll accept feihtthief's as he's got the least rep and thus needs it the most. :-) Thanks to everyone.

Will never cease to amaze how ridiculous TSQL's handling of booleans is. Great question BTW. — hajikelist, Feb 04 '17 at 09:07
It's like they've designed the language syntax to explicitly enforce an antipattern — Nathan Chappell, Sep 19 '22 at 09:11

score 99 · Accepted Answer · edited Oct 02 '11 at 20:25

99

Use the case construct:

select 'Test Name', 
    case when foo = 'Result' then 1 else 0 end 
    from bar where baz = (some criteria)

Also see the MSDN Transact-SQL CASE documentation.

edited Oct 02 '11 at 20:25

patrickmdnet

3,332
1
29
34

answered Jan 30 '09 at 22:30

feihtthief

6,403
6
30
29

score 22 · Answer 2 · answered Jan 30 '09 at 22:30

22

SELECT 'TestName', 
    CASE WHEN Foo = 'Result' THEN 1 ELSE 0 END AS TestResult
FROM bar 
WHERE baz = @Criteria

answered Jan 30 '09 at 22:30

Joel Coehoorn

399,467
113
570
794

Returns: (No column name) | TestResult / TestName | 1 – ono2012 Aug 23 '18 at 22:43

score 17 · Answer 3 · answered Sep 12 '15 at 00:48

17

You can also use:

select 
    'Test Name', 
    iif(foo = 'Result', 1, 0)
from bar 
where baz = (some criteria)

I know this was asked a while back, but I hope this helps someone out there.

answered Sep 12 '15 at 00:48

Sebastian G

221
2
3

1

IIF is only available from Sql Server 2012 on wards and Sql Azure as far as I know (https://msdn.microsoft.com/en-za/library/hh213574.aspx) – My Other Me May 11 '16 at 06:43
Although my situation was a little bit different, the IIF option gave me the in-line answer I needed for my evaluation I was doing. Thanks! – MeanJerry Feb 12 '19 at 22:06

score 17 · Answer 4 · answered Jan 30 '09 at 22:33

17

Use CASE:

SELECT 'Test Name' [col1],
  CASE foo
    WHEN 'Result' THEN 1
    ELSE 0
  END AS [col2]
FROM bar
WHERE baz = (some criteria)

answered Jan 30 '09 at 22:33

John

5,672
7
34
52

1

This one is slightly better because it uses the optimized equality 'case', i.e. `case foo when 'Result'` rather than `case when foo = 'Result'` – Mark Sowul Jun 02 '11 at 21:16
@MarkSowul: Can you provide some info on how the simple case is optimized over the searched case? I was always under the impression that a simple case gets evaluated or rewritten by the parser to a searched case, but based on this (http://sqlperformance.com/2014/06/t-sql-queries/dirty-secrets-of-the-case-expression) I think there are a lot real-world cases where you might be right. – My Other Me May 11 '16 at 07:10
I'm sorry, I don't remember at this point. I vaguely remember seeing different execution plans in some particular situation. This also would have been before SQL 2012. Ultimately though because the 'simple case' (case x when y) is more restricted the optimizer should have an easier time with it – Mark Sowul May 12 '16 at 02:15

Boolean Expressions in SQL Select list

4 Answers4

Linked