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
–Single IIF
SELECT IIF(@weather =‘Rainy’,‘Oh! its raining’,‘Sun is shinning..Enjoy’)
–MultipleIIF            
SELECT IIF(
      @weather = ‘Rainy’,IIF(
                          @umbrella = 1,‘Its raining but you cantake umbrella with you’
                                       ,‘Its raining, stay inside’)
                         ,‘Sun is shining..Enjoy’)
Advertisements

Posted on August 2, 2011, in SQL Server Denali, TSQL Tips n Tricks. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: