Advertisements
Home > SQL Built-in Functions, SQL Server 2012 > New CHOOSE() and IIF() functions introduced in SQL Server 2012 (Denali)

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


 

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:


Advertisements
  1. Garanphol
    August 7, 2018 at 6:46 am

    thank you

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: