SQL Server Denali CTP3 has introduces a new function IIF() to evaluates specified expression and returns specified true/false values.
IIF(), is kind of shorthand way of writing a CASE expression where we can expect only two result either true or false. This function is same as available in other languages.
IIF(), accepts three parameters,
- First Parameter is condition, which you want to evaluate,
- Value which will be returned, if condition returns true
- Value which will be returned, if condition returns fale
How to use IIF() function in SQL Server ?
In the following example, we are testing whether 1 < 0 or not. As this condition is false IIF() will return the value specified in third parameter. If would have tested 1 > 0 then we will get a value specified as a second parameter as conditioned returns true.
HOW IIF () uses NULL?
IIF can return NULL as return value.
Note: IIF() is translated into CASE expression, and thus it can be nested up to only 10 levels.