CODEWITHSUNDEEP
sqlt-sqlsql-server-2000bit
xcer
xcer

Reputation: 1707

Select a nullable bit with a default value

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.

Upvotes: 8

Views: 7105

Answers (4)

Ravi Ram
Ravi Ram

Reputation: 24488

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)

Upvotes: 0

Christoph
Christoph

Reputation: 4401

Take a look at Coalesce

Upvotes: 0

dcp
dcp

Reputation: 55434

SELECT coalesce(bit_column,0) bit_column

Upvotes: 6

spinon
spinon

Reputation: 10847

use the isnull function.

isnull(bit_column, 0)

Upvotes: 10

Related Questions