How to check a SP`s input parameters for NULL Values

Suppose you have a stored procedure with input parameters
and you need to check if all the input parameters are NOT NULL
or all are NULL.
There are many ways to check it (using AND or OR statements).
Additional way is to use string concatenation.
SQL Server has CONCAT_NULL_YIELDS_NULL option that
controls how NULL values are treated in string concatenation
(as null or empty string).
Consider the following example:

DECLARE @InputParameter1 VARCHAR(50),
@InputParameter2 VARCHAR(50),
@InputParameter3 VARCHAR(50),
@InputParameter4 SMALLINT

------------------------------------------------------------
-- Check if ALL the input parameters are NULL --
------------------------------------------------------------

-- "SET CONCAT_NULL_YIELDS_NULL OFF" tells to SQL Server to treat NULLs
-- as Empty String in String concatenation
SET CONCAT_NULL_YIELDS_NULL OFF
IF (@InputParameter1+@InputParameter2+@InputParameter3+
CAST(@InputParameter4 AS VARCHAR(50))) IS NULL
SELECT 'All Input Parameters are NULL'

-------------------------------------------------------------------
-- Check if ALL the input parameters are NOT NULL --
------------------------------------------------------------------

-- "SET CONCAT_NULL_YIELDS_NULL ON" tells to SQL Server to treat NULLs
-- as NULL in String concatenation
SET CONCAT_NULL_YIELDS_NULL ON
IF (@InputParameter1+@InputParameter2+@InputParameter3+
CAST(@InputParameter4 AS VARCHAR(50))) IS NOT NULL
SELECT 'All Input Parameters are NOT NULL'

You can also check whether at least one input parameter is not NULL
or at least one input parameter is NULL by using string
concatenation.

The source of this information is the following article:
http://wiki.lessthandot.com/index.php/How_To_Check_If_Any%2C_ALL_Or_No_Parameters_Have_A_NULL_Value
You can find more detailed example in above link.

Tweet