Boolean Expressions in SQL Select list

sql unit-testing tsql expression assert
Craig Walker picture Craig Walker · Jan 30, 2009 · Viewed 45.1k times · Source

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.

Answer

feihtthief picture feihtthief · Jan 30, 2009

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.

Related questions

Best way to test SQL queries

I have run into a problem wherein we keep having complex SQL queries go out with errors. Essentially this results in sending mail to the incorrect customers and other 'problems' like that. What is everyone's experience with creating SQL queries …

Read More
How to simulate a deadlock in SQL Server in a single process?

Our client side code detects deadlocks, waits for an interval, then retries the request up to 5 times. The retry logic detects the deadlocks based on the error number 1205. My goal is to test both the deadlock retry logic and deadlock …

Read More
django how to see sql query when running tests?

One of my django application unit test fails with DatabaseError: ORA-00942: table or view does not exist I would like to see actual SQL query that caused this error. Do you know how to achieve that?

Read More