SQL Server IIF: Syntactic Sugar that Doesn’t Rot Your Brain

It wasn’t until recently that I discovered a feature that I’ve missed since SQL Server 2012.  One of my recent projects involved converting a lot of old Visual FoxPro code.  That’s not quite as much fun as it sounds, as it turns out.  When things like ISNULL and. .F. and At and Atc behave differently or have no direct translation at all in from environment to the other hurt my head a little.  I expected the same level of annoyance when I saw the original programs peppered throughout with IIF statements.

My mood was seriously soured after puzzling my way through nested Scatters and Gathers to mirror the intent of the original code.  I cringed when I saw the IIFs because I knew that I’d need to rewrite everything as a CASE statement the way one would expect in T-SQL. Copying and pasting the first query into a SQL Management Studio instance, I braced myself for the inevitable pain.

Except that it all worked.

That’s an exaggeration.  There were a few FoxPro concepts that didn’t translate over and some convoluted logic that made me question my career choice, but SQL Management Studio detected the IIFs as keywords.  It surprised me more than a little when they behaved exactly as they did in the original program.  Refactoring those (sometimes nested two and three deep) conditional statements to CASEs and making sure that the original intent was preserved in each case scared me a little, so that was a good thing.  I had syntax that was familiar, almost exactly matching VB.NET and close enough to the?: in C# and even IF in Excel that there was very little need to mentally adjust.  On the other, IIF has developed a reputation for making it easy to write conditions that are impossible to read.  I’ve seen some nested IIFs that can challenge a person’s sanity.

I admit to giving my pair programming partner a certain amount of flak for his preference for IIF statements.  I doubt he’d been in the field prior to 2012, so he never lived in a world without them.  Despite my grumbling there’s a strong argument in favor of using that approach over CASE in some instances.  I appreciate using IIF in situations with a single test, several simple conditions chained together, or copying large chunks of code from something like VB.NET or FoxPro and much of the logic can be preserved for free.  Despite my grumpiness, IIF(condition, true value, false value) has a certain elegance at times.

I’ve rejected the overuse of language features that smell like syntactic sugar for a long time.  Shortcuts can prevent developers from understanding what’s really happening.  I live in mortal fear of losing what understanding I already have.  With IIF, I’m less likely to draw an absolute line in the sand.  It can contribute to sensible code that flows better than the alternatives, is easier to read, and saves brainpower for the really difficult questions.

Leave a Reply

Your email address will not be published. Required fields are marked *