Click here to monitor SSC
SQLServerCentral is supported by Redgate
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'    


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

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

DECLARE @variable VARCHAR(50) = 'I am not 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”.



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.


Leave a Comment

Please register or log in to leave a comment.