CODEWITHSUNDEEP
sql-servert-sqlsql-server-2014
diiN__________
diiN__________

Reputation: 7656

Convert IS NULL to BIT

This might be a very basic question but I just came over it while writing a query.

Why can't SQL Server convert a check for NULL to BIT? I was thinking about something like this:

DECLARE @someVariable INT = NULL;
-- Do something
SELECT CONVERT(BIT, (@someVariable IS NULL))

The expected outcome would then be either 1 or 0.

Upvotes: 15

Views: 19126

Answers (4)

Dana
Dana

Reputation: 719

Or use IIF (a little more readable than CASE):

CONVERT(BIT, IIF(@x IS NULL, 0, 1))

Upvotes: 6

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131403

In SQL the language, NULLs are not considered data values. They represent a missing/unknown state. Quoting from Wikipedia's article on SQL NULL:

SQL null is a state (unknown) and not a value. This usage is quite different from most programming languages, where null means not assigned to a particular instance.

This means that any comparison against that UNKNOWN value can only be UNKNOWN itself. Even comparing two NULLs can't return true: if both values are unknown, how can we say that they are equal or not?

IS NULL and IS NOT NULL are predicates that can be used in conditional expressions. That means that they don't return a value themselves. Therefore, they can't be "cast" to a bit , or treated as a boolean.

Basic SQL comparison operators always return Unknown when comparing anything with Null, so the SQL standard provides for two special Null-specific comparison predicates. The IS NULL and IS NOT NULL predicates (which use a postfix syntax) test whether data is, or is not, Null.

Any other way of treating nulls is a vendor-specific extension.

Finally, BIT is not a boolean type, it's just a single-bit number. An optional BOOLEAN type was introduced in SQL 1999 but only PostgreSQL implements it correctly, ie having TRUE, FALSE or UNKNOWN values.

Without a BOOLEAN type you can't really calculate the result of a conditional expression like A AND B or x IS NULL. You can only use functions like NULLIF or COALESCE to replace the NULL value with something else.

Upvotes: 1

paparazzo
paparazzo

Reputation: 45096

not a direct cast 

select cast(isnull(@null,1) as bit)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Use case:

SELECT CONVERT(BIT, (CASE WHEN @someVariable IS NULL THEN 1 ELSE 0 END))

Upvotes: 23

Related Questions