SQL Server CASE Statement with code examples

The SQL Server CASE statement is a conditional operation that can be used to provide if-then-else type of logic to T-SQL queries. The CASE statement can be used in SQL Server queries involving calculations, data transformation, and more.

1. Overview of the CASE Statement in SQL Server

The CASE operator in SQL Server is a powerful tool for creating more complex queries, particularly when you want to return different results based on conditions. The CASE statement works like an if-then-else block in other programming languages, allowing you to test conditions and return different results based on those conditions. Indeed, the generic syntax of a CASE statement in T-SQL is represented like this:

CASE 
   WHEN condition1 THEN result1 
   WHEN condition2 THEN result2 
   ...
   ELSE default_result 
END

Create the Sample Table

For our examples, let’s create a table Orders and populate it with some data, i.e., 7 lines with different order statuses.

-- Create the Orders table
CREATE TABLE [dbo].[Orders](
    [OrderID] [int] PRIMARY KEY,
    [CustomerID] [int] NOT NULL,
    [OrderAmount] [decimal](10,2) NOT NULL,
    [OrderStatus] [nvarchar](20) NOT NULL
);

-- Insert data into the Orders table
INSERT [dbo].[Orders] VALUES (1, 1, 100.00, 'Processed');
INSERT [dbo].[Orders] VALUES (2, 2, 200.00, 'Shipped');
INSERT [dbo].[Orders] VALUES (3, 1, 300.00, 'Returned');
INSERT [dbo].[Orders] VALUES (4, 3, 150.00, 'Shipped');
INSERT [dbo].[Orders] VALUES (5, 4, 250.00, 'Processed');
INSERT [dbo].[Orders] VALUES (6, 2, 350.00, 'Returned');
INSERT [dbo].[Orders] VALUES (7, 1, 400.00, 'Shipped');

2. Using the CASE Statement in a SELECT query

We can use a CASE query in T-SQL within the SELECT clause to change how the data is displayed based on conditions. For example, we can add a new column to our query that shows if the OrderAmount is ‘High’ or ‘Low’:

SELECT OrderID, 
       CustomerID, 
       OrderAmount,
       OrderStatus,
       CASE 
           WHEN OrderAmount > 300 THEN 'High'
           ELSE 'Low'
       END AS OrderValue
FROM Orders;

3. CASE Statement and WHERE Clause

We can use a SQL Server CASE statement in the WHERE section to dynamically filter records. For example, we can filter orders based on their status and amount:

SELECT OrderID, 
       CustomerID, 
       OrderAmount,
       OrderStatus
FROM Orders
WHERE (CASE 
           WHEN OrderStatus = 'Processed' AND OrderAmount > 200 THEN 1
           WHEN OrderStatus = 'Shipped' AND OrderAmount < 200 THEN 1
           ELSE 0
       END) = 1;

This query will return the orders that are either ‘Processed’ with an amount greater than 200 or ‘Shipped’ with an amount less than 200. On the same topic, here is how to compare data with SQL Server.

How to Compare Values in T-SQL?

4. Using the CASE keyword with Aggregate Functions

In this example, we will use the T-SQL CASE query with aggregate functions like SUM and COUNT to group orders based on certain predefined and selected conditions:

SELECT CustomerID,
       COUNT(OrderID) AS TotalOrders,
       SUM(CASE 
               WHEN OrderStatus = 'Processed' THEN 1
               ELSE 0
           END) AS ProcessedOrders,
       SUM(CASE 
               WHEN OrderStatus = 'Shipped' THEN 1
               ELSE 0
           END) AS ShippedOrders,
       SUM(CASE 
               WHEN OrderStatus = 'Returned' THEN 1
               ELSE 0
           END) AS ReturnedOrders
FROM Orders
GROUP BY CustomerID;

This query will return the total number of orders for each customer, as well as the number of orders in each status (Processed, Shipped, Returned).

5. SQL Server CASE Statement with ORDER BY

We can use the CASE statement in the ORDER BY clause to sort records based on certain conditions:

SELECT OrderID, 
       CustomerID, 
       OrderAmount,
       OrderStatus
FROM Orders
ORDER BY 
    CASE 
        WHEN OrderStatus = 'Processed' THEN 1
        WHEN OrderStatus = 'Shipped' THEN 2
        WHEN OrderStatus = 'Returned' THEN 3
        ELSE 4
    END;

This query will sort the orders by their status, with ‘Processed’ orders first, followed by ‘Shipped’, then ‘Returned’, and finally any other status.

These examples showcase the flexibility and power of the CASE statement in SQL Server, and how it can be used in various parts of a query to add conditional logic.

Conclusion on the T-SQL CASE statement

To conclude, the CASE statement is an incredibly powerful and flexible tool in SQL Server, enabling dynamic and conditionally evaluated SQL expressions. Whether it’s used in SELECT, UPDATE, DELETE, or INSERT statements, or within a WHERE clause, the CASE statement adds substantial versatility to your SQL syntax. It essentially allows us to create different paths and outcomes in our SQL statements based on specific conditions. However, mastering it requires practice and understanding of its syntax and possible use cases.

By continuously experimenting with and implementing the CASE statement in various scenarios, you can enhance the effectiveness and efficiency of your SQL queries, making your data manipulation tasks more streamlined and readable. Remember, good SQL development is not just about getting the right results, but also about making your code understandable and maintainable, also by other developers.