SQL with Manoj

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.

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

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
SELECT CASE 
	WHEN @x > @y THEN 'TRUE' 
	ELSE 'FALSE' 
END AS Result; -- FALSE
GO

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

–> Video: