SQL Server Denali: IIF Logical Function
If you have developed some sort of applications usingMicrosoft Access, then you are definitely familiar with “IIF” logical function.In SQL Server, prior to SQL Server Denali we can use “CASE” instead of “IIF” asthis logical function was not available. But in SQL Server Denali CTP3, “IIF”is available with same ease and functionality.
According to BOL”IIF is a shorthand way for writing a CASE statement. Itevaluates the Boolean expression passed as the first argument, and then returnseither of the other two arguments based on the result of the evaluation. Thatis, the true_value is returned if the Boolean expression is true, and thefalse_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 theCASE statement for Boolean expressions, null handling, and return types alsoapply to IIF.
The fact that IIF is translated into CASE also has an impact on other aspectsof the behavior of this function. Since CASE statements can nested only up tothe level of 10, IIF statements can also be nested only up to the maximum levelof 10. Also, IIF is remoted to other servers as a semantically equivalent CASEstatement, with all the behaviors of a remoted CASE statement.”
Let’s try it with simple example.
(Only applicable for SQL Server DenaliCTP 3)
DECLARE @weather VARCHAR(50) = ‘Rainy’, — Rain/Sunny
@umbrella BIT = 1 –1= Yes we have, 0=Nowe don’t have
SELECT IIF(@weather =‘Rainy’,‘Oh! its raining’,‘Sun is shinning..Enjoy’)
@weather = ‘Rainy’,IIF(
@umbrella = 1,‘Its raining but you cantake umbrella with you’
,‘Its raining, stay inside’)
,‘Sun is shining..Enjoy’)