IIF function with different data types as input params + SQL Server 2012

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

Example
1. 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

Example1
DECLARE @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
*/

Comments or Responses

Login to post response