IIF function's result arguments against NULL Constant and NULL expression

Posted by Bandi under Sql Server category on | Points: 40 | Views : 268
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


-- 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:

NOTE: Be aware of this behaviour of IIF function

Comments or Responses

Login to post response