Boolean Expressions in SQL Select list

Craig_Walker_3488 0
Viewed 55388

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.

assert expression unit-testing tsql sql
4 Answers
feihtthief_3200700 
SELECT 'TestName', 
    CASE WHEN Foo = 'Result' THEN 1 ELSE 0 END AS TestResult
FROM bar 
WHERE baz = @Criteria
Joel_Coehoorn_30430

Use CASE:

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