Inverse of COALESCE

is there a function in SQL Server 2005 that returns NULL [or a boolean value] if any of the arguments (of any type) is NULL, which would save me from writing IF 开发者_Python百科a IS NULL OR b IS NULL OR c IS NULL ....

Here is a moderately unpleasant way of doing it:

set ansi_nulls off
if (null in (a, b, c, d, e) print 'got a null'
set ansi_nulls on

Since NULLs propogate you could do:

(cola + colb + colc) is null

assuming all compatible data types

No, the closest you an get is NULLIF(), but that's not what you want. I'd just stick to using the OR statement here.

How about ...

SELECT
CASE WHEN NULLIF(ISNULL(@testA, 1), @testA) 
        + NULLIF(ISNULL(@testB, 1), @testB) 
        + NULLIF(ISNULL(@testC, 1), @testC) > 0
    THEN 'Got NULL'
    ELSE 'NO NULL'
END

I am not sure of SQL Server, but in similar database (I used hive here which is more close to traditional DBMS in terms of syntax) select isnull(concat(*)) from some_table; would help to check if any of the column is NULL.

Hope there is some way to play around this concept in SQL Server and my suggestion helps to solve the problem efficiently.