SQL Server offers a wide range of functions that can be used to manipulate and present data in various ways. In this blog post, we will explore some of these functions and discuss their usage and benefits.

1. CASE Statement

The CASE statement is a powerful tool in SQL Server that allows you to perform conditional logic and control the flow of your queries. It is not being replaced by any new functions introduced in recent versions of SQL Server. Instead, these new functions are just shorthand for the CASE statement.

For example, the TRY_PARSE() function can be used along with the CASE statement to handle errors in parsing data. Here is an example:

SELECT CASE WHEN TRY_PARSE('A100.000' AS INT) IS NULL THEN 'Error In Result' ELSE 'No Error' END AS ValueInt;

2. IIF Function

The IIF() function is another handy function that provides a shorthand for the CASE statement. It allows you to perform a simple conditional check and return a value based on the result. Here is an example:

SELECT IIF(-1 < 1, 'TRUE', 'FALSE') AS Result;

Internally, the IIF() function is implemented using the CASE statement. The execution plan of the IIF() function also shows that it uses the CASE statement.

3. CHOOSE Function

The CHOOSE() function is yet another shorthand for the CASE statement. It allows you to select a value from a list of options based on an index. Here is an example:

SELECT CHOOSE(DATEPART(dw, A.ModifiedDate), 'WEEKEND', 'Weekday', 'Weekday', 'Weekday', 'Weekday', 'Weekday', 'WEEKEND') WorkDay
FROM [Person].[Address] A
INNER JOIN [Person].[StateProvince] SP ON SP.StateProvinceID = A.StateProvinceID;

By using the CHOOSE() function, the T-SQL code looks cleaner and easier to read compared to the equivalent code using the CASE statement.

Conclusion

The CASE statement is a fundamental tool in SQL Server for data presentation and conditional logic. It is not being replaced by new functions like TRY_PARSE, IIF, or CHOOSE. Instead, these functions are just shortcuts that utilize the power of the CASE statement under the hood.

As you explore SQL Server and its functions, you will find that many new commands and functions will continue to be introduced, and they will often rely on the CASE statement for their functionality.

Now, here’s a question for you: Does the PIVOT function also use the CASE statement? Leave your answer in the comments below!