CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008
DForck42
DForck42

Reputation: 20387

determine if any values are null, if true then false, else true

I currently have a select statement that checks several columns to see if they have data. if any of them are null then i want a bit set to false. if none of them are null then i want a bit set to true. here's what i currently have:

select
cast(
        case when ChangeOrderNumber is null then 0 else 1 end *
        case when ClientName is null then 0 else 1 end *
        case when QuoteNumber  is null then 0 else 1 end *
        case when ClientNumber is null then 0 else 1 end *
        case when ServiceLine is null then 0 else 1 end *
        case when ServiceLineCode is null then 0 else 1 end *
        case when GroupLeader is null then 0 else 1 end *
        case when CreatedBy is null then 0 else 1 end *
        case when PTWCompletionDate is null then 0 else 1 end *
        case when BudgetedHours is null then 0 else 1 end *
        case when BudgetDollars is null then 0 else 1 end *
        case when InternalDeadlineDate is null then 0 else 1 end *
        case when ProjectDescription is null then 0 else 1 end *
        case when Sales is null then 0 else 1 end *
        case when Coop is null then 0 else 1 end *
        case when PassThrough is null then 0 else 1 end *
        case when POStatus is null then 0 else 1 end *
        case when PONumber is null then 0 else 1 end as bit
    ) 
    as Flag
from t

now, that code works, but it's a bit lengthy, i was wondering if anyone knew of a better way to do this. please note that there are several data types being checked.

further details: this code is in a view that is being looked at in an application for processing change orders. before a change order can be processed it must meet some data quality checks. this view shows if any of the required data is null.

Upvotes: 9

Views: 47666

Answers (8)

Xin
Xin

Reputation: 36600

Please use IIF() (need to be sql server 2012 or later) I really recommend:

IIF(column1 is null, '0', '1')

Upvotes: 4

Dana
Dana

Reputation: 729

Much simpler -- just use the COALESCE function, which returns the value in the first non-null column.

SELECT Flag = CASE 
    WHEN COALESCE (column1, column2, column3, ...) IS NULL THEN 0
    ELSE 1
   END
FROM MyTable

Upvotes: 0

SQLMenace
SQLMenace

Reputation: 135151

Just add them up since NULL + "something" is always NULL ...

CREATE TABLE #test(column1 int,column2 varchar(4),column3 float)

INSERT #test VALUES(2,'2',2)
INSERT #test VALUES(0,'1',0)
INSERT #test VALUES(null,'1',0)
INSERT #test VALUES(1,null,0)
INSERT #test VALUES(0,'1',null)
INSERT #test VALUES(null,null,null)

SELECT CASE 
WHEN column1 + column2 + column3 is NULL THEN 0 ELSE 1 END, *
FROM #test

from a post I created over 3 years ago ...

Keep in mind that if you have characters that are not numbers that you have to convert to varchar ...

INSERT #test VALUES(0,'abc',null)

Here is the conversion, no need to convert the varchar columns

SELECT CASE WHEN CONVERT(VARCHAR(100),column1) 
            + column2 
            +CONVERT(VARCHAR(100),column3)  is NULL THEN 0 ELSE 1 END,*
 FROM #test

Upvotes: 12

Anthony Faull
Anthony Faull

Reputation: 17997

Create a HasValue function that takes in a sql_variant and returns a bit. Then use bitwise AND in your SELECT clause.

CREATE FUNCTION dbo.HasValue(@value sql_variant) RETURNS bit
AS
BEGIN
    RETURN (SELECT COUNT(@value))
END

GO

SELECT dbo.HasValue(ChangeOrderNumber)
    & dbo.HasValue(ClientName)
    & dbo.HasValue(QuoteNumber)
    ...
    as [Flag]
FROM t

Upvotes: 1

DForck42
DForck42

Reputation: 20387

I think I might go with this solution unless someone comes up with a better one, inspired by @Alireza:

cast(
        case when (ChangeOrderNumber is null  or
        a.ClientName is null  or
        a.QuoteNumber  is null  or
        ClientNumber is null  or
        ServiceLine is null  or
        ServiceLineCode is null  or
        GroupLeader is null  or
        CreatedBy is null  or
        PTWCompletionDate is null  or
        BudgetedHours is null  or
        BudgetDollars is null  or
        InternalDeadlineDate is null  or
        ProjectDescription is null  or
        Sales is null  or
        Coop is null  or
        PassThrough is null  or
        POStatus is null  or
        PONumber is null) then 'false' else 'true'
        end as bit) as Flag

Upvotes: 6

Anthony Faull
Anthony Faull

Reputation: 17997

You could invert the logic.

SELECT
    CASE WHEN ChangeOrderNumber IS NOT NULL
            AND ClientName IS NOT NULL
            AND QuoteNumber IS NOT NULL
            ....
        THEN 1
        ELSE 0
    END [Flag]
FROM t

Upvotes: 2

RC_Cleland
RC_Cleland

Reputation: 2304

Or this:

 declare @test1 char(1)
 declare @test2 char(1)
 declare @outbit bit

 set @test1 = NULL
 set @test2 = 'some value'
 set @outbit = 'True'


 select @test1
 select @test2

 If @test1 + @test2 IS NULL set @outbit = 'False'
 Select @outbit

Upvotes: 0

Alireza
Alireza

Reputation: 5533

What about this one?

select not(a is null or b is null or ...)

Upvotes: 2

Related Questions