IIF() function is new to SQL Server family of functions. It is a brand new logical function being introduced with SQL Server 2012 that allows you to perform IF..THEN..ELSE condition within a single function. This helps in simplified code and easy to read conditions.
In earlier versions of SQL Server, we have used IF..ELSE and CASE..END CASE to perform logical conditional operations. However, IIF() can be used as a shorthand way of writing conditional CASE statements in a single function. It evaluates the expression passed in the first parameter with the second parameter depending upon the evaluation of the condition and returns either TRUE or FALSE.
In this example, we will evaluate the values of two variables and return the result of a variable which is bigger in value. |
DECLARE @M INT=40 DECLARE @V INT=30 SELECT IIF(@M > @V, M IS GREATER THAN V', 'V IS GREATER THAN M') GO; Executing the above T-SQL will return the following result: ------------------- M IS GREATER THAN V (1 row(s) affected) |
Like CASE statements which can be nested up to 10 levels, IIF() function can also nest up to 10 levels.
