Boolean Expressions in SQL Select list
SqlUnit TestingTsqlExpressionAssertSql Problem Overview
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.
Sql Solutions
Solution 1 - Sql
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.
Solution 2 - Sql
SELECT 'TestName',
CASE WHEN Foo = 'Result' THEN 1 ELSE 0 END AS TestResult
FROM bar
WHERE baz = @Criteria
Solution 3 - Sql
Use CASE:
SELECT 'Test Name' [col1],
CASE foo
WHEN 'Result' THEN 1
ELSE 0
END AS [col2]
FROM bar
WHERE baz = (some criteria)
Solution 4 - Sql
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.