Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Awesome new features in SQL Server 2011 “Denali”: IIF and CHOOSE functions

SQL Server 2011 “Denali” introduces two new features for flow control which may help you to keep your T-SQL cleaner than in previous versions. It is IIF and CHOOSE. These two function are very simple but it’s good to know they’re there.

IIF Function
Any developer should be aware of this function but prior SQL Server 2011 IIF workaround was:

DECLARE @variable VARCHAR(50) = 'I am not NULL'    

SELECT 

    CASE
        WHEN @variable IS NOT NULL THEN 'NOT_NULL'
        ELSE NULL
    END

But in SQL Server 2011 you can use true IIF function:

DECLARE @variable VARCHAR(50) = 'I am not NULL'    

SELECT IIF (@variable IS NOT NULL,'NOT_NULL',NULL)    

CHOOSE Function
CHOOSE function is not so typical but may help as well. Purpose of this function is to pick a value based on its index from given range.

Check this sample with subconscious communication :) :

SELECT CHOOSE ( 3, 'You', 'love', 'SQLTreeo')

Result will be “SQLTreeo”.

Nice.

Comments

Posted by Jason Brimhall on 3 August 2011

Does Denali also introduce the Switch function now?  Seems logical based on these new additions.

Posted by Jakub Dvorak on 3 August 2011

As far as I know, any other new flow control function will not be introduced.

Jakub

Leave a Comment

Please register or log in to leave a comment.