T-SQL 101: 96 Choosing from alternatives with IIF in SQL Server T-SQL

T-SQL 101: 96 Choosing from alternatives with IIF in SQL Server T-SQL

In my last T-SQL 101 post, I described the CASE statement. Until SQL Server 2012, that was the only real option that we had for choosing between alternate values. In SQL Server 2012, Microsoft gave us another option with the IIF function.

The IF function is very similar to the IF function in Microsoft Excel. It takes three parameters:

  • A boolean value to check (normally this is an expression)
  • A value that will be returned if the first parameter is true.
  • A value that will be returned if the first parameter is false.

In the main image above, you can see the equivalent CASE statement and the simplified IF function equivalent.

Nesting

Again similar to how IF works in Excel, you can nest IF functions. For example, you can write:

IF(Size = 'Large', 12, IF(Width = 13, 0, -12))

In this case, we’re saying that if the Size is Large, then 12 will be returned. But otherwise, if the Width is 13, then 0 will be returned. In all other cases, -12 will be returned.

Returned Data Type

The function looks at the data type of the second and third parameters (i.e. the values returned for true and false), and chooses the data type with the highest precedence. For example, if the second parameter is an int, and the third parameter is a bigint, then a bigint will be returned no matter which value is chosen.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2021-04-26