Denali CTP3 comes with the IIF function, if you have used VB or Excel then you already know how this function works. In essence this function is a shorter version of a CASE statement.
The syntax is as follows
IIF ( boolean_expression, true_value, false_value )
So instead of this
SELECT CASE WHEN 1 = 2 THEN 'equal' ELSE 'not equal' END AS Comp
We can do this
SELECT IIF(1=2,'equal','not equal') as Comp
Both of those will return not equal
Be aware that you can’t use NULL like in the example below
SELECT IIF(1=2,NULL ,NULL ) as calc
It throws an error (with a typo)
Msg 8133, Level 16, State 1, Line 1
At lease one of the result expressions in a CASE specification must be an expression other than the NULL constant.
If you use a variable then you can use NULL
declare @i int = NULL SELECT IIF(1=2,@i,@i) as calc
Here is some more info from Books On Line
_IIF is a shorthand way for writing a CASE statement. It evaluates the Boolean expression passed as the first argument, and then returns either of the other two arguments based on the result of the evaluation. That is, the true_value is returned if the Boolean expression is true, and the false_value is returned if the Boolean expression is false or unknown. true_value and false_value can be of any type. The same rules that apply to the CASE statement for Boolean expressions, null handling, and return types also apply to IIF.
The fact that IIF is translated into CASE also has an impact on other aspects of the behavior of this function. Since CASE statements can nested only up to the level of 10, IIF statements can also be nested only up to the maximum level of 10. Also, IIF is remoted to other servers as a semantically equivalent CASE statement, with all the behaviors of a remoted CASE statement.
Here is a nested (silly) example
SELECT IIF(1=2,'equal',IIF(4=2,'equal','not equal')) as Comp
Here is another example that combines IIF with TRY_CONVERT to return if a value can be converted to a specific data type
SELECT IIF(TRY_CONVERT(float,'bla')IS NULL,'Cast failed','Cast succeeded') UNION SELECT IIF(TRY_CONVERT(float,'1')IS NULL,'Cast failed','Cast succeeded')
I welcome this function, anything that makes the code shorter is welcomed with open arms by me.