IIF Fucntions takes 3 arguments as below,
IIF( boolean_expression, true_value, false_value) It raises exception if the first argument is not boolean operation.
Example1. Comparing two strings ( i.e. 2nd & 3rd arguments are of same type data
DECLARE @SearchStr VARCHAR(100) = 'HR'
SELECT IIF( @SearchStr IN ( 'IT', 'HR') , 'Belongs to IT/HR', 'Not Belongs to IT/HR')
2. IIF returns the result's data type with the highest precedence from 2nd & 3rd arguments
Example1DECLARE @SearchStr VARCHAR(100) = 'IT'
SELECT IIF( @SearchStr IN ( 'IT', 'HR') , 100, 0.23 ) ResultWithHighestPrecedence
/* Output
ResultWithHighestPrecedence
100.00
*/
Here, 2nd argument is INTEGER; 3rd argument is DECIMAL
So resultant data type is DECIMAL ( 100.0) even though the result is of INTEGER
Example2
DECLARE @SearchStr VARCHAR(100) = 'IT' --match
SELECT IIF( @SearchStr IN ( 'IT', 'HR') , GETDATE(), 0.23 ) ResultWithHighestPrecedence
/*
ResultWithHighestPrecedence
2015-03-20 10:55:06.620
*/
GO
DECLARE @SearchStr VARCHAR(100) = 'UnMatch'
SELECT IIF( @SearchStr IN ( 'IT', 'HR') , GETDATE(), 0.23 ) ResultWithHighestPrecedence
The result should be 0.23, but because of highest precedence data type DATETIME the result of above IIF function
/* ResultWithHighestPrecedence
1900-01-01 05:31:12.000
*/