CASE (Transact-SQL)

Simple CASE expression:

The simple CASE expression operates by comparing the first expression to the expression in each WHEN clause for equivalency. If these expressions are equivalent, the expression in the THEN clause will be returned.

• Allows only an equality check.

• Evaluates input_expression, and then in the order specified, evaluates input_expression = when_expression for each WHEN clause.

• Returns the result_expression of the first input_expression = when_expression that evaluates to TRUE.

• If no input_expression = when_expression evaluates to TRUE, the SQL Server Database Engine returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

Searched CASE expression:

• Evaluates, in the order specified, Boolean_expression for each WHEN clause.

• Returns result_expression of the first Boolean_expression that evaluates to TRUE.

• If no Boolean_expression evaluates to TRUE, the Database Engine returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

Within a SELECT statement, a simple CASE expression allows for only an equality check; no other comparisons are made. The following example uses the CASE expression to change the display of product line categories to make them more understandable.

    USE AdventureWorks2008R2;
    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

Within a SELECT statement, the searched CASE expression allows for values to be replaced in the result set based on comparison values. The following example displays the list price as a text comment based on the price range for a product.

    USE AdventureWorks2008R2;
    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

CASE provides functionality that is similar to the IIf function in Microsoft Access. The following example shows a simple query that uses 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 following example uses CASE to provide an output value for the TelephoneSpecialInstructions column in the AdventureWorks2008R2 view Person.vAdditionalContactInfo.

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

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

    SELECT BusinessEntityID, SalariedFlag
    FROM HumanResources.Employee
    ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID END DESC
            ,CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END;
    GO

    SELECT BusinessEntityID, LastName, TerritoryName, CountryRegionName
    FROM Sales.vSalesPerson
    WHERE TerritoryName IS NOT NULL
    ORDER BY CASE CountryRegionName WHEN 'United States' THEN TerritoryName
             ELSE CountryRegionName END;

The following example uses the CASE expression in an UPDATE statement to determine the value that is set for the column VacationHours for employees with SalariedFlag 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 AdventureWorks2008R2;
GO
UPDATE HumanResources.Employee
SET VacationHours = 
    ( CASE
         WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40
         ELSE (VacationHours + 20.00)
       END
    )
OUTPUT Deleted.BusinessEntityID, Deleted.VacationHours AS BeforeValue, 
       Inserted.VacationHours AS AfterValue
WHERE SalariedFlag = 0; 

The following example uses the CASE expression in a SET statement in the table-valued function dbo.GetContactInfo. In the AdventureWorks2008R2 database, all data related to people is stored in the Person.Person table. For example, the person may be an employee, vendor representative, or a customer. The function returns the first and last name of a given BusinessEntityID 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 BusinessEntityID column in the Employee, Vendor, or Customer tables.

    USE AdventureWorks2008R2;
    GO
    CREATE FUNCTION dbo.GetContactInformation(@BusinessEntityID int)
    RETURNS @retContactInformation TABLE 
    (
    BusinessEntityID int NOT NULL,
    FirstName nvarchar(50) NULL,
    LastName nvarchar(50) NULL,
    ContactType nvarchar(50) NULL,
    PRIMARY KEY CLUSTERED (BusinessEntityID 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 
        @BusinessEntityID = BusinessEntityID, 
        @FirstName = FirstName, 
        @LastName = LastName
    FROM Person.Person 
    WHERE BusinessEntityID = @BusinessEntityID;

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

            -- Check for vendor
            WHEN EXISTS(SELECT * FROM Person.BusinessEntityContact AS bec
                WHERE bec.BusinessEntityID = @BusinessEntityID) 
                THEN 'Vendor'

            -- Check for store
            WHEN EXISTS(SELECT * FROM Purchasing.Vendor AS v          
                WHERE v.BusinessEntityID = @BusinessEntityID) 
                THEN 'Store Contact'

            -- Check for individual consumer
            WHEN EXISTS(SELECT * FROM Sales.Customer AS c 
                WHERE c.PersonID = @BusinessEntityID) 
                THEN 'Consumer'
        END;

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

    RETURN;
END;
GO

SELECT BusinessEntityID, FirstName, LastName, ContactType
FROM dbo.GetContactInformation(2200);
GO
SELECT BusinessEntityID, FirstName, LastName, ContactType
FROM dbo.GetContactInformation(5);