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.
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 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”.