From Wiki

Let's say you have a procedure that accepts 6 parameters. depending on if these parameters are all null, all not null, some null and some not null you want to do different things. How can you test this the easiest? This depends what you are testing for but COALESCE and concatenation are the easiest (read least to type) to do.

So let's start with all nulls You can do a bunch of ANDs You can concatenate into a string but you have to first set CONCAT_NULL_YIELDS_NULL OFF because the default CONCAT_NULL_YIELDS_NULL ON will return NULL if even one value is NULL You can also union the parameters and do a NOT EXIST NOT NULL test Finally you can use COALESCE which will return NULL if ALL the values are NULL

Next up is to test that at least one value is not NULL You can do a bunch of Ors You can concatenate into a string but you have to first set CONCAT_NULL_YIELDS_NULL OFF because the default CONCAT_NULL_YIELDS_NULL ON will return NULL if even one value is NULL You can also union the parameters and do a EXIST NOT NULL test Finally you can use COALESCE which will not return NULL if ANY of the values is not NULL

Next up is to test that there are no NULLs You can do a bunch of ANDs You can also union the parameters and do a NOT EXIST NULL test Finally you can use COALESCE which will not return NULL if ANY of the values is not NULL

The final test is to test that at least one value is NULL You can do a bunch of Ors You can concatenate into a string and leave the default CONCAT_NULL_YIELDS_NULL ON because CONCAT_NULL_YIELDS_NULL ON will return NULL if even one value is NULL You can also union the parameters and do a EXIST NOT NULL test

Contributed by: --SQLDenis 16:24, 30 May 2008 (GMT)

Part of SQL Server Programming Hacks

Section NULLS