Case statement in SQL Server

The SQL Server CASE statement is a conditional statement that returns a single value based on the evaluation of a statement. CASE expressions can be used in SQL anywhere an expression can be used like SELECT list, WHERE clause, HAVING clause, IN list, DELETE and UPDATE statements. CASE statement can also be nested. This provides a lot of pliability for evaluating multiple expressions.

SQL Case statement can be used in 2 forms:

  1. SQL CASE statement with simple expression to compare and get results.
  2. SQL CASE statement with search or comparison expression to get results.
  1. SQL CASE statement with simple expression to compare and get results

    A simple CASE expression operates by comparing the first expression to the expression in each WHEN clause for equivalency.

    Syntax

    CASE expression
        WHEN expression1 THEN expression1
        [[WHEN expression2 THEN expression2] [...]]
        [ELSE expression N]
    END
    

    Example

    SELECT CASE [Section]
        WHEN 1 THEN 'One'
        WHEN 2 THEN 'Two'
        WHEN 3 THEN 'Three'
        WHEN 4 THEN 'Four'
        WHEN 5 THEN 'Five'
        ELSE 'Greater than Five'
    END as [Sec_Word] FROM [Table_Test]
    
  2. SQL CASE statement with search or comparison expression to get results

    A search or comparison CASE expression allows comparison operators, and the use of AND and/or OR between each Boolean expression.

    Syntax

    CASE
        WHEN Boolean_expression1 THEN expression1
        [[WHEN Boolean_expression2 THEN expression2] [...]]
        [ELSE expression N]
    END
    

    Example

    SELECT CASE
        WHEN [Section] < 5 THEN 'Less than Five'
        WHEN [Section] = 5 THEN 'Equal to Five'
        WHEN [Section] > 5 THEN 'Greater than Five'
    END as [Sec_Word] FROM [Table_Test]
    

Hope you will find this post helpful. For any suggestion or query please feel free to post your valuable comments. Thanks.

Leave a Reply

Your email address will not be published. Required fields are marked *