Boolean expression as column value in transact sql

Question

In most RDBMS:es, this work:

select (5 > 3)

and evaluates to true. It doesn't work in MS Transact SQL and the only workaround I've found is to write:

select case when 5 > 3 then 1 else 0 end

Which kind of sucks because it is much more verbose. Is there a better way to write the above kind of checks?

Answer 1

If your program often requires such case constructs you could create your set of functions that will have user functions like Bool_IsGreater(left, right) that will return you your desired 0 or 1.

SQL Server doesn't support boolean value type anyway even for basic column use.

If you will need performance and those 5 and 3 values come naturally from some select query you might want to create a new column and set its value to 1 or 0 by trigger or something, which could help with performance.

Answer 2

You could write it as a scalar-valued function, but it will be very slow on large datasets.

Answer 3

If the problem is arithmetic comparison:

select (5 - 3)

Then at the application level test for < or = or > 0.