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.