Editorials

Selective Functions

SQL Server 2012 introduced two functions that have been around for a while in other SQL engines that are really quite powerful. They are the Immediate If (IIF) and Choose functions. Either function can be replaced with the CASE function. However, they are easier to read and understand exactly what is intended in the query.

IIF evaluates a Boolean expression. Based on the evaluation, it returns a user provided value for true I the expression evaluates to true, or a user provided value for false if the expression evaluates to false.

IIF (1=1, ‘True’, ‘False’)

This would be the equivalent of a case statement

CASE WHEN 1=1 THEN ‘True’ ELSE ‘False’ END

The choose statement is probably more practical, because it can be more cumbersome to implement using a CASE function. The choose statement evaluates a number n, and returns the nth option from a list based on the number.

SELECT CHOOSE(DATEPART(month, getdate()), 'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')

Running this query returns the text represented by the current month. If you want to see it work, replace GETDATE() with any date you choose. Or simple replace the whole DATEPART function with the number of a month.

A case statement would look as follows, and is much longer.

SELECT CASE DATEPART(month, getdate())

WHEN 1 THEN 'Jan'

WHEN 2 THEN 'Feb'

WHEN 3 THEN 'Mar'

WHEN 4 THEN 'Apr'

WHEN 5 THEN 'May'

WHEN 6 THEN 'Jun'

WHEN 7 THEN 'Jul'

WHEN 8 THEN 'Aug'

WHEN 9 THEN 'Sep'

WHEN 10 THEN 'Oct'

WHEN 11 THEN 'Nov'

WHEN 12 THEN 'Dec'

ELSE '' END

Just for completeness, you can also get similar results using the SUBSTRING function, using the query that follows.


SELECT SUBSTRING('JanFebMarAprMayJunJulAugSepOctNovDec', DATEPART(month, getdate()) *3 -2, 3)

I think you’ll probably agree that the CHOOSE function is superior in that it provides a clear expression of the intention of the program.

Cheers,

Ben

Facebooktwittergoogle_plusredditpinterestlinkedinmail
  • David Eaton

    Wow, IIF! Long ,Long time ago, in the FoxPro days before Microsoft got it, I used this all the time. Not sure how I missed this, probably spending too much time working on server performance…….

  • Sadiq

    Thanks Ben, I was really missing this IIF function and till I read your article I was not aware that MS has added it since SQL Server 2012. Seems I need to spare some time to start reading books again;)

  • Ben

    I think we miss this stuff because there hasn’t been a LOT of new syntax candy in SQL Server for the last 5 releases. So, we quit looking for it.

    That’s not to say MS hasn’t done anything in the Engine. It has been radically improved with things like Column Store Indexes, or In Memory Tables.

    But, sometimes things like Syntax are more meaningful.