Using IIF instead of CASE

For years the primary means of expressing conditional logic in SQL Server has been the CASE statement, which allows for powerful set-based logic and I personally love.  However, sometimes you just have a quick two-way decision that you need to make in your data set or just with variables, and CASE syntax feels a bit overkill.  Especially if you also code in a programming language that gives you the IIF function.

Thank you SQL Server 2012.  The IIF has arrived.

Now you can (and I already have, extensively) deal with situations where if the Boolean answer to a formula is true, do this, else do that using IIF.  Here is a quick, simple example.

Declare @Value1 Int = 1,

@Value2 Int = 2

Print '@Value1=' + Convert(varchar(1),@Value1)

Print '@Value2=' + Convert(varchar(1),@Value2)

Print IIF(@Value1 = @Value2,'They Equal','They Do Not Equal')

Set @Value2 = 1

Print '@Value1=' + Convert(varchar(1),@Value1)

Print '@Value2=' + Convert(varchar(1),@Value2)

Print IIF(@Value1 = @Value2,'They Equal','They Do Not Equal')

This is not revolutionary, but it does make for a nice shortcut in true/false scenarios, and it is another step toward shared syntax with external programs.  A small but good addition to SQL 2012.

Until next time, may well defined requirements be at your back and realistic timelines lie before you.