In SQL Server, how can I use the result of a comparison operator as a logical value?

Question

I would like to compute something in an SQL Server result set that is the result of the value of comparison operators on other columns, something like

select (a > 2 AND b > 2 AND (c > 2 OR or c is null)) as "Reject"...

I just can't find any syntax that works.

`CAST(CASE WHEN ... THEN 1 ELSE 0 END AS BIT)`. T-SQL doesn't support boolean-typed expressions (or event `BIT` literals, for that matter), and yes, that's dumb. — Jeroen Mostert, Mar 24 '21 at 16:25

score 1 · Accepted Answer · edited Mar 24 '21 at 16:31

1

SQL Server doesn't have a boolean type. Perhaps you intend:

select (case when a > 2 and b > 2 and (c > 2 or c is null)
             then 1 else 0
        end) as is_reject

edited Mar 24 '21 at 16:31

critical_error

6,306
3
14
16

answered Mar 24 '21 at 16:27

Gordon Linoff

1,242,037
58
646
786

It *does* have a `boolean` type, you just can't select it. There are many places where `boolean` appears, such as `where`,`having`,`case`,`if`,`while` – Charlieface Mar 24 '21 at 17:59
1

@Charlieface: per the docs you are technically correct, which as we all know is the best kind of correct, but on the other hand the *only* place where you can find this supposed "type" is in the docs, and even they can't keep their story straight. Is there a data type named `Boolean`? [Yes](https://docs.microsoft.com/sql/t-sql/language-elements/comparison-operators-transact-sql), also [no](https://docs.microsoft.com/sql/t-sql/data-types/data-types-transact-sql). I prefer the "there are Boolean expressions and let's not talk about a type unless we pretend it has no explicit name" angle. YMMV. – Jeroen Mostert Mar 24 '21 at 21:23
@JeroenMostert I think the "no" doc you reference refers only to data types that can be stored, selected or returned. `boolean` is still a data type. See also https://learn.microsoft.com/en-gb/sql/t-sql/language-elements/logical-operators-transact-sql?view=sql-server-ver15 It's all over query plans as well if you ever look at them – Charlieface Mar 24 '21 at 21:31

score 0 · Answer 2 · answered Mar 24 '21 at 16:26

0

SELECT CASE
   WHEN A>2 AND B>2 AND (C>2 OR CIS NULL) THEN 'REJECT'
    ELSE 'NO REJECT'
END

answered Mar 24 '21 at 16:26

Sergey

4,719
1
6
11

Olivier Jacot-Descombes · Answer 3 · 2021-03-24T16:44:00.617

SQL Server has a limited concept of Booleans. They are used within logical expressions in statements like IF.. ELSE and WHILE and in WHERE clauses, but cannot be assigned to a variable or column directly. Use a CASE statement instead

SELECT 
    CASE WHEN a > 2 AND b > 2 AND (c > 2 OR c IS NULL)
        THEN 1
        ELSE 0
    END AS "Reject"...

There is a BIT data type that can be either 0 or 1 and is commonly used to store truth values.

Also, SQL Server uses a three valued logic. See: Example of three valued logic in SQL Server

In SQL Server, how can I use the result of a comparison operator as a logical value?

3 Answers3