With the CASE statement you can add conditional logic to your T-SQL code. T-SQL 2012 has certain functions that can be seen as CASE shortcuts. With these functions you can quickly use some CASE functionality and as a surplus, because they are functions you can use them everywhere where expressions are allowed. The examples are written against the AdventureWorks2012 database:

ISNULL

The ISNULL(‘Null_Expression’,‘Replace_Value’) function evaluates the value of the ‘Null_Expression’ and if the result is NULL it returns the ‘Replace_Value’ value. Otherwise the ‘Null_Expression’ value is returned:

SELECT TOP 10 ISNULL(MiddleName,'NA')AS NoNullMiddleName FROM Person.Person

In the result we see that all the NULL values are replaced by the ‘NA’ value. Values that were not NULL keep their value:

COALESCE

This function accepts 2 or more arguments and will return the first non NULL value. In the next example I use a common table expression and fill it with items with a weight and no size, items with a size and no weight and items with a size and weight:

WITH Product_CTE (name, size, weight) AS
(
	SELECT TOP 10 name, size, weight FROM Production.Product
	WHERE size IS NOT NULL AND weight IS NULL
	UNION
	SELECT TOP 10 name, size, weight FROM Production.Product
	WHERE weight IS NOT NULL AND size IS NULL
	UNION
	SELECT TOP 10 name, size, weight FROM Production.Product
	WHERE weight IS NOT NULL AND size IS NOT NULL
)
Select name, size, weight, COALESCE(size,CAST(weight AS VARCHAR(10))) AS coalesceresult
 FROM Product_CTE

In the result you see that the size is shown in the coalesceresult column unless the original value was NULL. In that case the weight value is returned:

IIF

Just like in SSIS and MS Access, SQL Server 2012 now supports the IIF(‘logical_expression’,‘expression_if_true’,‘expression_if_false’) function. So if the ‘logical_expression evaluates to TRUE the ‘expression_if_true’ value is shown, otherwise the ‘expression_if_false’ is executed:

SELECT IIF(Title LiKE '%s.','Miss','Mister') AS Title, FirstName 
 FROM Person.Person
 WHERE Title IS NOT NULL

Since IIF supports the true/false expressions you can even put logical expressions in their place to create single line CASE scenarios to have more possibilities. For ease of use and general readability of your code, I would not recommend this. The result of the previous query:

CHOOSE

CHOOSE(‘index’,‘expr1’,‘expr2’,…,‘exprn’) is also known in MS Access and new in SQL Server 2012 and gives the possibility to use an index number and the result of the corresponding index expression:

SELECT FirstName, LastName, MiddleName, CHOOSE(2,FirstName, LastName, MiddleName) AS Chosen
 FROM Person.Person

In normal coding you would dynamically assign a value to the index with a variable. The result of the above query is:

Conclusion

The CASE shortcuts can be very useful in coding scenarios but note that only COALESCE is ANSI standard. IIF and CHOOSE are added to the T-SQL 2012 language to ease the migration from MS Access to SQL Server.