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.