Select a nullable bit with a default value

Question

I need to select a nullable bit column in a view, but use a default value of FALSE whenever the value is NULL. (For other reasons, I can't add the default value on the source table itself.) Here is what I am doing.

CAST 
(
    CASE 
    WHEN bit_column IS NULL THEN 0 
    ELSE bit_column  
END 
    AS BIT
) AS bit_column,
...

I have to do this on four columns, so I'm wondering if there is a better/more efficient way to do this.

Brandibrandice

Answer 1

use the isnull function.

isnull(bit_column, 0)

Verbalism

Answer 2

SELECT coalesce(bit_column,0) bit_column

Onaonager

What datatype will be sent to the client? int or bit? – Haplite

A bit will be sent to the client. – Brandibrandice

Good question, I researched it and found this link: databases.aspfaq.com/database/coalesce-vs-isnull-sql.html From there, "COALESCE works more like a CASE expression, which returns a single datatype depending on precendence and accommodating all possible outcomes". So I'm not sure. But on the client side, 0 or 1 will fit in either a bit or an int, so I'm not sure it matters. – Onaonager

Thank you for the link. It appears that since I always want the result to have the datatype of the first parameter, I should use ISNULL. ISNULL also allows me to see True or False (preferred over 1 or 0) when I browse the view in SQL Server Management Studio. So I am going to accept the ISNULL answer. However, I can see many situations where COALESCE is a better choice. Thank you for your help. – Brandibrandice

SELECT coalesce(bit_column, cast(0 as bit)) bit_column – Palladian

@Shannon Severance: that is just too far to avoid ISNULL – Haplite

@gbn: That's why it's a comment and not an answer. But knowing another option can't hurt. – Palladian

Answer 3

Take a look at Coalesce

Way

Answer 4

For T-SQL use

SELECT coalesce(bit_column, cast(0 as bit)) bit_column

In a code example,

 , examStatus.text
 , COALESCE(examStatus.archived, cast(0 as bit))

If examStatus.archived is NULL it will default to 0 (aka false)

Downpipe