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.
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.