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