IIF function returns exception if we pass both return arguments ( 2nd & 3rd arguments) as NULL constant; where as returns the correct output if we pass two NULL expressions/columns
Sample:
-- NULL constants
Declare @DeptName VARCHAR(100) = 'IT'
SELECT IIF( @DeptName = 'IT' , NULL, NULL)
/*
Msg 8133, Level 16, State 1, Line 2
At least one of the result expressions in a CASE specification must be an expression other than the NULL constant.
*/
-- NULL expression/columns
Declare @DeptName VARCHAR(100) = 'IT'
,@SecondArgument int = NULL
, @ThirdArgument INT = NULL
SELECT IIF( @DeptName = 'IT' , @SecondArgument, @ThirdArgument)
/*
Out put:
NULL
*/
NOTE: Be aware of this behaviour of IIF function