THE IIF statement – SQL has it at last.

G’day All,

While I was writing my last blog post about the new SQL SERVER 2012 CHOOSE statement, it also came to my notice that we additionally have the IIF statement.

Again, this was added in 2012.

I’d assume that most people would be familiar with this statement as the IIF functionality is available in a wide variety of programming constructs.

So, just for the record we take a look at the IIF statement (otherwise know as an Inline IF) and see how it work.

The IIF statement takes three arguments, the first is an expression that will evaluate to either true or false. The second argument is the value that will be returned should expression that was supplied for the first argument evaluates to true. Conversely, the third argument will be the value returned if the expression that was supplied for the first argument evaluates to false.

To put this in simple, straightforward language, it’s just a shorthand way of writing a CASE statement – or even an IF..ELSE construct – as long as you’re just looking for 2 values, true or false :)

SELECT IIF(19 > 18 /*true*/ , 'It''s true' , 'it''s false') "SELECT IIF(19 > 18 /*true*/ , 'It''s true' , 'it''s false')";
SELECT IIF(19 > 20 /*false*/ , 'It''s true' , 'it''s false') "SELECT IIF(19 > 20 /*false*/ , 'It''s true' , 'it''s false')";

and here’s the result
IIF

Have a great day

Cheers

Martin.

Tags:

About Martin Catherall

Martin Catherall is Senior SQL consultant at SQL Down Under, based in Melbourne, Australia. Martin is also a Microsoft Data Platform MVP and Regional Mentor (RM) for the Professional Association for SQL Server. Prior to relocating to Australia he was extremely active in the Christchurch, New Zealand data community - founding the local SQL Server user group and organizing SQL Saturday Christchurch - which later became SQL Saturday South Island. He likes learning interesting stuff about processing and storing data and passing on his knowledge. In his spare time he likes to learn guitar and hang out with his two young sons Callum Glen and Robert Michael.