jump to navigation

Looking into IIF and CHOOSE SQL Server 2012 logical functions July 15, 2012

Posted by fofo in Sql Server, SQL Server 2012.
Tags: ,
trackback

I will be writing a new series of posts about the new T-SQL functions that are available to us in SQL Server 2012.

In this rather sort post I will demonstrate with a hands-on examples how to use 2 new logical functions in SQL Server 2012, CHOOSE and IIF.

You can have a look in another blog post of mine related to built-in SQL functions.

You can use any edition of SQL Server of 2012 you want. SQL Server Express edition will do just fine.Download the Express edition, here

In my case I will use Developer edition of 2012 for these examples.

I will start with the CHOOSE function.This function returns an item from a specified set of  items for the specified index.You can think of it as an array kind of thing.

Launch SQL Server 2012 and in a new query window type and execute the code below


SELECT CHOOSE ( 3, 'Chelsea', 'Man Utd', 'Liverpool', 'Arsenal' ) AS 'The Best team';

But what happens if the index is not an integer? In that case the value is converted to an integer value.

Execute the code below


SELECT CHOOSE ( 3.3, 'Chelsea', 'Man Utd', 'Liverpool', 'Arsenal' ) AS 'The Best team';

You can use a variable to define the index value.

Execute the code below


declare @index as int = 3

SELECT CHOOSE ( @index, 'Chelsea', 'Man Utd', 'Liverpool', 'Arsenal' ) AS 'The Best team';

In this last example I will be using a table from the AdventureWorks2012 OLTP database.

You can download it from codeplex.

I will be using CHOOSE to concatenate values from the Person.Person table.Obviously this is not the best way to do this but I just want to demonstrate the CHOOSE function

Type and execute the code below


USE AdventureWorks2012
GO

SELECT CHOOSE(1,Title,FirstName,MiddleName,LastName) + ' ' + CHOOSE(2,Title,FirstName,MiddleName,LastName) + ' ' + CHOOSE(3,Title,FirstName,MiddleName,LastName) + ' .' + CHOOSE(4,Title,FirstName,MiddleName,LastName) FROM Person.Person;

Let’s move on to the IIF function.The IIF function returns one of two values depending if the first expression evaluates to TRUE or FALSE.If the first expression evaluates to TRUE the first value is displayed,if not the second value is displayed.

Type and execute the code below


SELECT IIF(DATEPART(WEEKDAY,GETDATE())=3 ,'Today is Tuesday','It is not Tuesday') as 'Day of the Week' ;

Please note that the start of the week is Sunday with index 1. So today as I am writing this post (Saturday night) the above script will return “It is not Tuesday” and that is because the first argument of the expression returned “FALSE”.

Ιn this last example I will use the Product table of the AdventureWorks database.In this example we want to produce a small report where every product that starts with ‘Touring’ should return back ‘Out Of Stock‘.

Type and execute the following code


SELECT [ProductID]
,[Name]
,[ProductNumber]
,[MakeFlag]
,[ListPrice]
,[Size]
,IIF((LEFT(Name,7)= 'Touring'), 'Out Of stock','In stock') as StockAvailability
FROM [AdventureWorks2012].[Production].[Product]

Please have a look at the results!!!

Hope it helps!!!!

Comments»

1. Dot Net Rules : Looking into IIF and CHOOSE SQL Server 2012 logical functions - July 15, 2012

[…] with a hands-on examples how to use 2 new logical functions in SQL Server 2012, CHOOSE and IIF. (read more) Share Posted: Κυριακή, 15 Ιουλίου 2012 12:16 πμ από το μέλος […]

2. Looking into the new conversion functions in SQL Server 2012 « DOT NET RULES - July 15, 2012

[…] SQL functions.You can have a look into the new logical functions introduced in SQL Server 2012, here […]

3. Looking into Concat string function in SQL Server 2012 « DOT NET RULES - July 16, 2012

[…] SQL functions.You can have a look into the new logical functions introduced in SQL Server 2012, here […]


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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: