Click here to Skip to main content

CASE Expression (T-SQL) Explanation with Examples – SQL SERVER

This article will give you an idea about how to use CASE expression and examples, in T-SQL or as a formula of a particular column.

For your reference, I’ve also included a small table of contents.


Introduction

CASE expression returns one of multiple possible results from a list of conditions. CASE expression can be used in any statement or clause that allows a valid expression. For example, CASE expression can be used in:

  • SELECT/SELECT list clause;
  • UPDATE, SET and DELETE statements;
  • IN, WHERE, ORDER BY, and HAVING clauses.

CASE expression has two basic formats:

1.        The simple CASE expression

Simple CASE expression operates by checking the one expression against multiple expressions in each WHEN clause for equivalency. And return the result of the true expression, if no expression is compared or evaluates to TRUE, then the ELSE clause returns the result if an ELSE clause is specified, or a NULL value of no ELSE clause is specified.

Syntax:

CASE input_expression
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END

Arguments:

  • input_expression is in any valid expression and evaluated when the simple CASE format is used.
  • WHEN when_expression is a simple expression to which input_expression is compared when the simple CASE format is used. when_expression is any valid expression. The data types of input_expression and each when_expression must be the same or must be an implicit conversion.
  • THEN result_expression is the expression returned when input_expression and when_expression are equaled and evaluates to TRUE.
  • ELSE, else_result_expression is the expression which returns the value when no comparison operation evaluates to TRUE. If this argument is omitted and no comparison operation evaluates to TRUE, CASE expression returns NULL. else_result_expression is any valid expression.The data types of else_result_expression and any result_expression must be the same or must be an implicit conversion.

Return Types:

CASE expression returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression.

2.        The searched CASE expression

The searched CASE expression evaluates in the order specified Boolean expression for each WHEN clause; and returns the result expression of the first Boolean expression that evaluates to TRUE. If no expression is compared or evaluates to TRUE, then the ELSE clause returns the result if an ELSE clause is specified, or a NULL value of no ELSE clause is specified. The searched CASE expression allows use of the AND, OR, comparison operators between each Boolean expression.

Syntax:

CASE
WHEN Boolean_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END

Arguments:

  • WHEN boolean_expression is an expression which check and return TRUE of FALSE values.
  • THEN result_expression is the expression returned when boolean_expression is evaluated to TRUE.
  • ELSE else_result_expression is the expression returned if no operation evaluates to TRUE. If this argument is omitted and no comparison operation evaluates to TRUE, CASE expression returns NULL. else_result_expression is any valid expression.The data types of else_result_expression and any result_expression must be the same or must be an implicit conversion.

Return Types:

CASE expression returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression.

Examples: 

A. Simple CASE expression in a SELECT statement 

When using simple CASE expression with SELECT statement, it allows only one equality check and no other comparisons are made. In example below, CASE expression is used to change the display of product line categories to make them more understandable.

USE AdventureWorks;
GO
SELECT ProductNumber, Category =
CASE ProductLine
	WHEN 'R' THEN 'Road'
	WHEN 'M' THEN 'Mountain'
	WHEN 'T' THEN 'Touring'
	WHEN 'S' THEN 'Other sale items'
	ELSE 'Not for sale'
END,
Name
FROM Production.Product
ORDER BY ProductNumber;
GO

B. Searched CASE expression in a SELECT statement

Using CASE expression within a SELECT statement, we are allowed for values to be replaced in the result set based on comparison values. The example below displays the list price as a text comment based on the price range for a product.

USE AdventureWorks;
GO
SELECT ProductNumber, Name, 'Price Range' =
CASE
WHEN ListPrice = 0 THEN 'Mfg item - not for resale'
WHEN ListPrice < 50 THEN 'Under $50' WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250' WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'
ELSE 'Over $1000'
END
FROM Production.Product
ORDER BY ProductNumber ;
GO

C. Using CASE to replace the one line if function (IIf)

CASE expression can provide functionality that is similar to the one line if function (IIf) which is used in Microsoft Access. The following example shows a simple query that uses one line if function (IIf) to provide an output value for the TelephoneInstructions column in an Access table that is named db1.ContactInfo.

SELECT FirstName, LastName, TelephoneNumber, 
     IIf(IsNull(TelephoneInstructions),"Any time",
     TelephoneInstructions) AS [When to Contact]
FROM db1.ContactInfo;

The next example shows how to use CASE to provide an output value for the TelephoneSpecialInstructions column in the AdventureWorks database to view Person.vAdditionalContactInfo in SQL Server.

USE AdventureWorks;
GO
SELECT FirstName, LastName, TelephoneNumber, 'When to Contact' = 
     CASE
          WHEN TelephoneSpecialInstructions IS NULL THEN 'Any time'
          ELSE TelephoneSpecialInstructions
     END
FROM Person.vAdditionalContactInfo;

D. Using CASE in an ORDER BY clause

The following examples uses the CASE expression in an ORDER BY clause to determine the sort order of the rows based on a given column value. In the first example, the value in the SalariedFlag column of the HumanResources.Employee table is evaluated. Employees that have the SalariedFlag set to 1 are returned in order by theEmployeeID in descending order. Employees that have the SalariedFlag set to 0 are returned in order by the EmployeeID in ascending order. In the second example, the result set is ordered by the column TerritoryName when the column CountryRegionName is equal to ‘United States’ and by CountryRegionName for all other rows.

First Example:
SELECT EmployeeID, SalariedFlag
FROM HumanResources.Employee
ORDER BY CASE SalariedFlag WHEN 1 THEN EmployeeID END DESC
        ,CASE WHEN SalariedFlag = 0 THEN EmployeeID END;
GO
Second Example:
SELECT SalesPersonID, LastName, TerritoryName, CountryRegionName
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL
ORDER BY CASE CountryRegionName WHEN 'United States' THEN TerritoryName
         ELSE CountryRegionName END;

E. Using CASE in an UPDATE statement

The following example uses the CASE expression in an UPDATE statement to determine the value that is set for the column VacationHours for employees withSalariedFlag set to 0. When subtracting 10 hours from VacationHours results in a negative value, VacationHours is increased by 40 hours; otherwise,VacationHours is increased by 20 hours. The OUTPUT clause is used to display the before and after vacation values.

USE AdventureWorks;
GO
UPDATE HumanResources.Employee
SET VacationHours = 
    ( CASE
         WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40
         ELSE (VacationHours + 20.00)
       END
    )
OUTPUT Deleted.EmployeeID, Deleted.VacationHours AS BeforeValue, 
       Inserted.VacationHours AS AfterValue
WHERE SalariedFlag = 0;

F. Using CASE in a SET statement

The following example uses the CASE expression in a SET statement in the table-valued function dbo.GetContactInfo. In the AdventureWorks database, all data related to people is stored in the Person.Contact table. For example, the person may be an employee, vendor representative, retail store representative, or a consumer. The function returns the first and last name of a given ContactID and the contact type for that person.The CASE expression in the SET statement determines the value to display for the column ContactType based on the existence of the ContactID column in the Employee, StoreContact, VendorContact, or Individual (consumer) tables.

USE AdventureWorks;
GO
CREATE FUNCTION dbo.GetContactInformation(@ContactID int)
RETURNS @retContactInformation TABLE 
(
	ContactID int NOT NULL,
	FirstName nvarchar(50) NULL,
	LastName nvarchar(50) NULL,
	ContactType nvarchar(50) NULL,
    PRIMARY KEY CLUSTERED (ContactID ASC)
) 
AS 
-- Returns the first name, last name and contact type for the specified contact.
BEGIN
    DECLARE 
        @FirstName nvarchar(50), 
        @LastName nvarchar(50), 
        @ContactType nvarchar(50);

    -- Get common contact information
    SELECT 
        @ContactID = ContactID, 
        @FirstName = FirstName, 
        @LastName = LastName
    FROM Person.Contact 
    WHERE ContactID = @ContactID;

    SET @ContactType = 
        CASE 
            -- Check for employee
            WHEN EXISTS(SELECT * FROM HumanResources.Employee AS e 
                WHERE e.ContactID = @ContactID) 
                THEN 'Employee'

            -- Check for vendor
            WHEN EXISTS(SELECT * FROM Purchasing.VendorContact AS vc 
                    INNER JOIN Person.ContactType AS ct 
                    ON vc.ContactTypeID = ct.ContactTypeID 
                WHERE vc.ContactID = @ContactID) 
                THEN 'Vendor Contact'

            -- Check for store
            WHEN EXISTS(SELECT * FROM Sales.StoreContact AS sc 
                    INNER JOIN Person.ContactType AS ct 
                    ON sc.ContactTypeID = ct.ContactTypeID 
                WHERE sc.ContactID = @ContactID) 
                THEN 'Store Contact'

            -- Check for individual consumer
            WHEN EXISTS(SELECT * FROM Sales.Individual AS i 
                WHERE i.ContactID = @ContactID) 
                THEN 'Consumer'
        END;

    -- Return the information to the caller
    IF @ContactID IS NOT NULL 
    BEGIN
        INSERT @retContactInformation
        SELECT @ContactID, @FirstName, @LastName, @ContactType;
    END;

    RETURN;
END;
GO
SELECT ContactID, FirstName, LastName, ContactType
FROM dbo.GetContactInformation(2200);
GO
SELECT ContactID, FirstName, LastName, ContactType
FROM dbo.GetContactInformation(5);

G. Using CASE in a HAVING clause

The following example uses the CASE expression in a HAVING clause to restrict the rows returned by the SELECT statement. The statement returns the the maximum hourly rate for each job title in the HumanResources.Employee table. The HAVING clause restricts the titles to those that are held by men with a maximum pay rate greater than 40 dollars or women with a maximum pay rate greater than 42 dollars.

USE AdventureWorks;
GO
SELECT Title, MAX(ph1.Rate)AS MaximumRate
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeePayHistory AS ph1 ON e.EmployeeID = ph1.EmployeeID
GROUP BY Title
HAVING (MAX(CASE WHEN Gender = 'M' 
        THEN ph1.Rate 
        ELSE NULL END) > 40.00
     OR MAX(CASE WHEN Gender  = 'F' 
        THEN ph1.Rate  
        ELSE NULL END) > 42.00)
ORDER BY MaximumRate DESC;

Remarks

Nesting of CASE expression is allowed for only 10 levels in SQL Server.

The CASE expression cannot be used to control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures.

Resources

Conclusion

To conclude, we have two format of CASE expression, Simple CASE expression which operates and compare one expression against multiple expression in each WHEN clause for equivalency. and Searched CASE expression which evaluates in the order specified Boolean expression for each WHEN clause; and returns the result expression of the first Boolean expression that evaluates to TRUE. Also the searched CASE expression allow use of AND, OR and comparison operators between each Boolean expression.

Feel free to comment, suggest and give your feedback.

The post CASE Expression (T-SQL) Explanation with Examples – SQL SERVER appeared first on Noor Ahmad Feroozi's Blog.


Web01 | 2.8.160208.1 | Advertise | Privacy
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service