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

Have a great day

Cheers

Martin.