Выражение CASE (Transact-SQL)

Оценка списка условий и возвращение одного из нескольких возможных выражений результатов.

Выражение CASE имеет два формата:

  • простое выражение CASE для определения результата сравнивает выражение с набором простых выражений;

  • поисковое выражение CASE для определения результата вычисляет набор логических выражений.

Оба формата поддерживают дополнительный аргумент ELSE.

Выражение CASE может использоваться в любой инструкции или предложении, которые допускают допустимые выражения. Например, выражение CASE можно использовать в таких инструкциях, как SELECT, UPDATE, DELETE и SET, а также в таких предложениях, как select_list, IN, WHERE, ORDER BY и HAVING.

Значок ссылки на разделСинтаксические обозначения в Transact-SQL

Синтаксис

Simple CASE expression: 
CASE input_expression 
     WHEN when_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END 
Searched CASE expression:
CASE
     WHEN Boolean_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END

Аргументы

  • input_expression
    Выражение, полученное при использовании простого формата функции CASE. Аргумент input_expression представляет собой любое допустимое выражение.

  • WHEN when_expression
    Простое выражение, с которым сравнивается аргумент input_expression при использовании простого формата функции CASE. Аргумент when_expression представляет собой любое допустимое выражение. Типы данных аргумента input_expression и каждого из выражений when_expression должны быть одинаковыми или неявно приводимыми друг к другу.

  • THEN result_expression
    Выражение, возвращаемое, если сравнение выражений input_expression и when_expression дает в результате TRUE или выражение Boolean_expression вычисляется в TRUE. Аргумент result expression представляет собой любое допустимое выражение.

  • ELSE else_result_expression
    Это выражение, возвращаемое, если ни одна из операций сравнения не дает в результате TRUE. Если этот аргумент опущен и ни одна из операций сравнения не дает в результате TRUE, функция CASE возвращает NULL. Аргумент else_result_expression представляет собой любое допустимое выражение. Типы данных аргумента else_result_expression и любого из аргументов result_expression должны быть одинаковыми или неявно приводимыми друг к другу.

  • WHEN Boolean_expression
    Это логическое выражение, полученное при использовании поискового формата функции CASE. Аргумент Boolean_expression представляет собой любое допустимое логическое выражение.

Типы возвращаемых данных

Возвращает выражение с наивысшим приоритетом из набора выражений result_expressions и необязательного выражения else_result_expression. Дополнительные сведения см. в разделе Приоритет типов данных (Transact-SQL).

Возвращаемые значения

Простое выражение CASE:

Простое выражение CASE сравнивает первое выражение с выражением в каждом предложении WHEN. Если эти выражения эквивалентны, то возвращается выражение в предложении THEN.

  • Допускается только проверка равенства.

  • Вычисляет выражение input_expression, затем в указанном порядке сравнивает значения выражений input_expression и when_expression для каждого предложения WHEN.

  • Возвращает выражение result_expression, соответствующее первому предложению WHEN, для которого операция сравнения input_expression = when_expression вычисляется в TRUE.

  • Если ни одна из операций input_expression = when_expression не вычисляется в TRUE, компонент SQL Server Database Engine возвращает выражение else_result_expression, если указано предложение ELSE, или значение NULL, если предложение ELSE не указано.

Поисковое выражение CASE:

  • Вычисляет в указанном порядке выражения Boolean_expression для каждого предложения WHEN.

  • Возвращает выражение result_expression, соответствующее первому предложению WHEN, для которого выражение Boolean_expression имеет значение TRUE.

  • Если ни одно выражение Boolean_expression не вычисляется в TRUE, компонент Database Engine возвращает выражение else_result_expression, если указано предложение ELSE, или значение NULL, если предложение ELSE не указано.

Замечания

SQL Server допускает применение в выражениях CASE не более 10 уровней вложенности.

Выражение CASE нельзя использовать для управления потоком выполнения инструкций Transact-SQL, блоков инструкций, определяемых пользователем функций и хранимых процедур. Список методов управления выполнением см. в разделе Язык управления потоком (Transact-SQL).

Инструкция CASE последовательно вычисляет условия и останавливается после того, как обнаружено первое выполняющееся условие. В некоторых случаях выражение вычисляется до того, как инструкция CASE получит результаты выражения в качестве входных данных. При вычислении таких выражений возможны ошибки. Сначала вычисляются агрегатные выражения, которые используются в аргументах WHEN инструкции CASE, а затем выполняется инструкция CASE. Например, следующий запрос при формировании значения статистического выражения MAX выдаст ошибку деления на ноль. Она возникает еще до вычисления выражения CASE.

WITH Data (value) AS 
( 
SELECT 0 
UNION ALL 
SELECT 1 
) 
SELECT 
   CASE 
      WHEN MIN(value) <= 0 THEN 0 
      WHEN MAX(1/value) >= 100 THEN 1 
   END 
FROM Data ;

Необходимо основываться только на порядке вычисления условий WHEN для скалярных выражений (включая некоррелированные вложенные запросы, которые возвращают скалярные значения), но не для агрегатных выражений.

Примеры

А. Использование инструкции SELECT с простым выражением CASE

При использовании в инструкции SELECT простое выражение CASE позволяет выполнить только проверку на равенство. Другие проверки не выполняются. В следующем примере выражение CASE используется для изменения способа отображения категорий линейки продуктов с целью сделать их более понятными.

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

Б. Использование инструкции SELECT с поисковым выражением CASE

При использовании в инструкции SELECT поисковое выражение CASE позволяет заменять значения в результирующем наборе в зависимости от результатов сравнения. В следующем примере отображается список цен в виде текстового комментария, основанного на диапазоне цен для продукта.

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 для замены функции IIf, используемой в Microsoft Access

Возможности выражения CASE схожи с возможностями функции IIf СУБД Microsoft Access. Следующий пример показывает простой запрос, использующий функцию IIf для задания выходного значения столбца TelephoneInstructions таблицы Access с именем db1.ContactInfo.

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

В следующем примере выражение CASE используется для задания выходного значения столбца TelephoneSpecialInstructions в представлении База данных AdventureWorks2008R2, 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;

Г. Использование выражения CASE в предложении ORDER BY

В следующем примере выражение CASE используется в предложении ORDER BY, чтобы определить порядок сортировки строк на основе значения заданного столбца таблицы. В первом примере вычисляется значение столбца SalariedFlag таблицы HumanResources.Employee. Сотрудники, для которых столбец SalariedFlag имеет значение 1, возвращаются в порядке EmployeeID (по убыванию). Сотрудники, для которых столбец SalariedFlag имеет значение 0, возвращаются в порядке EmployeeID (по возрастанию). Во втором примере результирующий набор упорядочивается по столбцу TerritoryName, если столбец CountryRegionName содержит значение «United States», и по столбцу CountryRegionName в остальных случаях.

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;

Д. Использование выражения CASE в инструкции UPDATE

В следующем примере выражение CASE используется в инструкции UPDATE, чтобы определить значение, установленное в столбце VacationHours для сотрудников, у которых столбец SalariedFlag имеет значение 0. Если при вычитании 10 часов из VacationHours получается отрицательное значение, VacationHours увеличивается на 40 часов. В противном случае значение VacationHours увеличивается на 20 часов. С помощью предложения OUTPUT отображаются исходная и обновленная продолжительности отпуска.

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; 

Е. Использование выражения CASE в инструкции SET

В следующем примере выражение CASE используется в инструкции SET в возвращающей табличное значение функции dbo.GetContactInfo. В базе данных База данных AdventureWorks2008R2 все данные, связанные с людьми, хранятся в таблице Person.Person. Например, человек может быть сотрудником, представителем поставщика или заказчиком. Функция возвращает имя и фамилию человека с заданным BusinessEntityID и соответствующий тип контактного лица. Выражение CASE в инструкции SET определяет отображаемое значение для столбца ContactType в зависимости от наличия значения в столбце BusinessEntityID в таблицах Employee, Vendor или Customer.

   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);

Ж. Использование выражения CASE в предложении HAVING

В следующем примере выражение CASE используется в предложении HAVING, чтобы ограничить строки, возвращаемые инструкцией SELECT. Инструкция возвращает максимальную почасовую ставку для каждой должности в таблице HumanResources.Employee. Предложение HAVING ограничивает должности, оставляя только те, которые заняты мужчинами с максимальной почасовой ставкой более 40 долларов или женщинами с максимальной почасовой ставкой более 42 долларов.

USE AdventureWorks2008R2;
GO
SELECT JobTitle, MAX(ph1.Rate)AS MaximumRate
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeePayHistory AS ph1 ON e.BusinessEntityID = ph1.BusinessEntityID
GROUP BY JobTitle
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;