CASE Statement in MSSQL

In Microsoft SQL Server (MSSQL), the CASE statement is used to provide conditional logic within SQL queries. It allows you to perform different actions based on different conditions.

Here's the basic syntax of the CASE statement:

CASE

    WHEN condition1 THEN result1

    WHEN condition2 THEN result2

    ...

    ELSE default_result

END

- condition1, condition2, etc., are the conditions to be evaluated.

- result1, result2, etc., are the values or expressions returned when the corresponding condition is true.

- default_result is the value or expression returned if none of the conditions are true (optional).

Let's see an example to illustrate how to use the CASE statement:

Suppose we have a table employees with columns id, name, and salary. We want to categorize employees based on their salary into three categories: "Low", "Medium", and "High".

CREATE TABLE employees (

    id INT PRIMARY KEY,

    name VARCHAR(100),

    salary DECIMAL(10, 2)

);

INSERT INTO employees (id, name, salary) VALUES

(1, 'John', 50000.00),

(2, 'Alice', 70000.00),

(3, 'Bob', 45000.00),

(4, 'Jane', 90000.00);

Now, let's use a CASE statement to categorize the employees based on their salary:

SELECT 

    name,

    salary,

    CASE 

        WHEN salary < 50000 THEN 'Low'

        WHEN salary >= 50000 AND salary < 80000 THEN 'Medium'

        ELSE 'High'

    END AS salary_category

FROM 

    employees;

This query will categorize employees based on their salary into three categories: "Low", "Medium", and "High". The results will look like this:

| name  | salary   | salary_category |

|-------|----------|-----------------|

| John  | 50000.00 | Medium          |

| Alice | 70000.00 | Medium          |

| Bob   | 45000.00 | Low             |

| Jane  | 90000.00 | High            |

As you can see, the CASE statement evaluated the salary for each employee and categorized them accordingly.