As a developer, you may often find yourself using IF…ELSE logic in your .NET code to handle different scenarios. However, as your data grows, the performance of your code may start to suffer. In such cases, it is recommended to move the logic to a stored procedure in SQL Server. But how do you convert the IF…ELSE logic to T-SQL? Let’s explore the solution using the CASE statement.

Example 1: Simple IF…ELSE Logic

Suppose you have the following logic in your code:

IF -1 < 1
    THEN 'TRUE'
    ELSE 'FALSE'

In SQL Server, you can convert this logic to the CASE statement as follows:

-- SQL Server 2008 and earlier version solution
SELECT CASE WHEN -1 < 1 THEN 'TRUE' ELSE 'FALSE' END AS Result;

-- SQL Server 2012 solution
SELECT IIF(-1 < 1, 'TRUE', 'FALSE') AS Result;

The CASE statement allows you to evaluate conditions and return different values based on the result. In this example, we check if -1 is less than 1 and return ‘TRUE’ if it is, otherwise ‘FALSE’.

Example 2: Logic Based on Table Columns

Now let’s consider a scenario where your logic is based on the columns of a table. Suppose you have the following logic:

IF BusinessEntityID < 10
    THEN FirstName
ELSE IF BusinessEntityID > 10
    THEN PersonType

In T-SQL, you can convert this logic to the CASE statement as follows:

SELECT CASE WHEN BusinessEntityID < 10 THEN FirstName
            WHEN BusinessEntityID > 10 THEN PersonType
       END AS Col, BusinessEntityID, Title, PersonType
FROM Person.Person p;

The CASE statement allows you to handle different conditions and return corresponding values based on the result. In this example, we check if the BusinessEntityID is less than 10 and return the FirstName, otherwise if it is greater than 10, we return the PersonType.

Example 3: Complex Logic with Multiple Columns

In some cases, your logic may be based on multiple columns and involve complex conditions. Let’s consider the following logic:

IF BusinessEntityID < 10
    THEN FirstName
ELSE IF BusinessEntityID > 10 AND Title IS NOT NULL
    THEN PersonType
ELSE IF Title = 'Mr.'
    THEN 'Mister'
ELSE
    'No Idea'

In T-SQL, you can convert this logic to the CASE statement as follows:

SELECT CASE WHEN BusinessEntityID < 10 THEN FirstName
            WHEN BusinessEntityID > 10 AND Title IS NOT NULL THEN PersonType
            WHEN Title = 'Mr.' THEN 'Mister'
            ELSE 'No Idea'
       END AS Col, BusinessEntityID, Title, PersonType
FROM Person.Person p;

The CASE statement allows you to handle complex conditions by evaluating multiple columns and returning the appropriate value based on the result.

By converting your IF…ELSE logic to the CASE statement in SQL Server, you can improve the performance of your code and make it more efficient. The CASE statement provides a flexible and powerful way to handle different conditions and return the desired results.

I hope this solution helps you in converting your IF…ELSE logic to the CASE statement in SQL Server. If you have any further questions or need more information, feel free to reach out.