CASE Expressions in SQL Server

A CASE expression in SQL Server is a way to perform an IF-THEN-ELSE logic in a query. It allows you to evaluate a condition and return a specific value based on that condition.

Syntax:

CASE WHEN {condition} THEN {result} [WHEN {condition} THEN {result}] [ELSE {result}] END;

Example 1: Simple CASE Expression

Let's say we have a table named Employees with a column named Salary, and we want to categorize the salaries into three groups: Low, Medium, and High.

Table Structure:

CREATE TABLE Employees ( EmployeeID int, Name varchar(50), Salary decimal(10, 2) );

Sample Data:

INSERT INTO Employees (EmployeeID, Name, Salary) VALUES (1, 'John Doe', 40000.00), (2, 'Jane Doe', 60000.00), (3, 'Bob Smith', 80000.00), (4, 'Alice Johnson', 30000.00);

CASE Expression:

SELECT Name, Salary, CASE WHEN Salary < 40000.00 THEN 'Low' WHEN Salary < 70000.00 THEN 'Medium' ELSE 'High' END AS SalaryCategory FROM Employees;

Result:

Example 2: Searched CASE Expression

A searched CASE expression allows you to specify multiple conditions to evaluate.

Example:

SELECT OrderID, OrderDate, CASE WHEN OrderDate < '2010-01-01' THEN 'Old' WHEN OrderDate >= '2010-01-01' AND OrderDate < '2015-01-01' THEN 'Middle' ELSE 'New' END AS OrderAge FROM Orders;

Example 3: Using CASE with Aggregate Functions

You can use a CASE expression with aggregate functions like SUM, AVG, and COUNT.

Example:

SELECT SUM(CASE WHEN Salary > 50000.00 THEN Salary ELSE 0 END) AS TotalHighSalaries FROM Employees;

This query calculates the total salary of employees who earn more than $50,000.