How To Check If Any, ALL Or No Parameters Have A NULL Value

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


--ALL NULL
DECLARE @Param1 VARCHAR(10),
@Param2 VARCHAR(10),
@Param3 VARCHAR(10),
@Param4 VARCHAR(10),
@Param5 VARCHAR(10),
@Param6 INT


IF @Param1 IS NULL
AND @Param2 IS NULL
AND @Param3 IS NULL
AND @Param4 IS NULL
AND @Param5 IS NULL
AND @Param6 IS NULL
PRINT 'ALL NULL'

SET CONCAT_NULL_YIELDS_NULL OFF
IF (SELECT @Param1+ @Param2+ @Param3+ @Param4+ @Param5+ CONVERT(VARCHAR(10),@Param6)) IS NULL
PRINT 'ALL NULL'


IF NOT EXISTS (SELECT * FROM (SELECT @Param1 AS Param UNION ALL
SELECT @Param2 UNION ALL
SELECT @Param3 UNION ALL
SELECT @Param4 UNION ALL
SELECT @Param5 UNION ALL
SELECT CONVERT(VARCHAR(10),@Param6) ) X WHERE Param IS NOT NULL)
PRINT 'ALL NULL'



IF (SELECT COALESCE(@Param1,@Param2,@Param3,@Param4,@Param5,CONVERT(VARCHAR(10),@Param6))) IS NULL
PRINT 'ALL 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

--Some Non Nulls
DECLARE @Param1 VARCHAR(10),
@Param2 VARCHAR(10),
@Param3 VARCHAR(10),
@Param4 VARCHAR(10),
@Param5 VARCHAR(10),
@Param6 INT

SELECT @Param1 ='A'

SET CONCAT_NULL_YIELDS_NULL OFF
IF (SELECT @Param1+ @Param2+ @Param3+ @Param4+ @Param5+ CONVERT(VARCHAR(10),@Param6)) IS NOT NULL
PRINT 'At least One Value is not NULL'



IF @Param1 IS NOT NULL
OR @Param2 IS NOT NULL
OR @Param3 IS NOT NULL
OR @Param4 IS NOT NULL
OR @Param5 IS NOT NULL
OR @Param6 IS NOT NULL
PRINT 'At least One Not NULL'


IF EXISTS (SELECT * FROM (SELECT @Param1 AS Param UNION ALL
SELECT @Param2 UNION ALL
SELECT @Param3 UNION ALL
SELECT @Param4 UNION ALL
SELECT @Param5 UNION ALL
SELECT CONVERT(VARCHAR(10),@Param6) ) X WHERE Param IS NOT NULL)
PRINT 'At least One Not NULL'

IF (SELECT COALESCE(@Param1,@Param2,@Param3,@Param4,@Param5,CONVERT(VARCHAR(10),@Param6))) IS NOT NULL
PRINT 'At least One 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

--No NULLS
DECLARE @Param1 VARCHAR(10),
@Param2 VARCHAR(10),
@Param3 VARCHAR(10),
@Param4 VARCHAR(10),
@Param5 VARCHAR(10),
@Param6 INT

SELECT @Param1 ='A',
@Param2 ='A',
@Param3 ='A',
@Param4 ='A',
@Param5 ='A',
@Param6 =1


IF @Param1 IS NOT NULL
AND @Param2 IS NOT NULL
AND @Param3 IS NOT NULL
AND @Param4 IS NOT NULL
AND @Param5 IS NOT NULL
AND @Param6 IS NOT NULL
PRINT 'No NULLs'

IF NOT EXISTS (SELECT * FROM (SELECT @Param1 AS Param UNION ALL
SELECT @Param2 UNION ALL
SELECT @Param3 UNION ALL
SELECT @Param4 UNION ALL
SELECT @Param5 UNION ALL
SELECT CONVERT(VARCHAR(10),@Param6) ) X WHERE Param IS NULL)
PRINT 'No NULLs'


SET CONCAT_NULL_YIELDS_NULL ON
IF (SELECT @Param1+ @Param2+ @Param3+ @Param4+ @Param5+ CONVERT(VARCHAR(10),@Param6)) IS NOT NULL
PRINT 'No NULLs'

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

--Some Nulls
DECLARE @Param1 VARCHAR(10),
@Param2 VARCHAR(10),
@Param3 VARCHAR(10),
@Param4 VARCHAR(10),
@Param5 VARCHAR(10),
@Param6 INT


SELECT @Param1 ='A',
@Param2 ='A',
@Param3 ='A',
@Param4 =null,
@Param5 ='A',
@Param6 =1

IF @Param1 IS NULL
OR @Param2 IS NULL
OR @Param3 IS NULL
OR @Param4 IS NULL
OR @Param5 IS NULL
OR @Param6 IS NULL
PRINT 'At least One NULL'

SET CONCAT_NULL_YIELDS_NULL ON
IF (SELECT @Param1+ @Param2+ @Param3+ @Param4+ @Param5+ CONVERT(VARCHAR(10),@Param6)) IS NULL
PRINT 'At least One NULL'
ELSE
PRINT 'No NULLs'

IF EXISTS (SELECT * FROM (SELECT @Param1 AS Param UNION ALL
SELECT @Param2 UNION ALL
SELECT @Param3 UNION ALL
SELECT @Param4 UNION ALL
SELECT @Param5 UNION ALL
SELECT CONVERT(VARCHAR(10),@Param6) ) X WHERE Param IS NULL)
PRINT 'At least One NULL'