Archive for the ‘SQL Built-in Functions’ Category

New CHOOSE() and IIF() functions introduced in SQL Server 2012 (Denali)

August 6, 2012 1 comment


1. The CHOOSE() function provides you array like feature where 1st parameter specifies the index and rest parameters are the array elements, this returns the element at the specified index from a list of elements.

SELECT CHOOSE ( 3, 'Apple', 'Mango', 'Banana', 'Kiwi' ) AS Result; -- Banana
SELECT CHOOSE ( 2, 'Manoj', 'Saurabh', 'Andy', 'Dave' ) AS Result; -- Saurabh


2. The IIF() function is a good replacement of CASE statement, it returns either of the two values passed to 2nd and 3rd parameter on evaluation of boolean expression to the 1st parameter.

-- IIF()
DECLARE @x int = 10;
DECLARE @y int = 20;
SELECT IIF ( @x > @y, 'TRUE', 'FALSE' ) AS Result; -- FALSE

-- CASE equivalent
	WHEN @x > @y THEN 'TRUE' 
END AS Result; -- FALSE

Note: IIF() function is internally converted to CASE expression by SQL engine and can be nested upto 10 levels like CASE.

–> Video: