CASE (Transact-SQL)

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse AnalyticsPDW (Analytics Platform System)

Avalia uma lista de condições e retorna uma das várias expressões de resultado possíveis.

A expressão CASE tem dois formatos:

  • A expressão CASE simples compara uma expressão com um conjunto de expressões simples para determinar o resultado.

  • A expressão CASE pesquisada avalia um conjunto de expressões boolianas para determinar o resultado.

Os dois formatos dão suporte a um argumento ELSE opcional.

CASE pode ser usada em qualquer instrução ou cláusula que permita uma expressão válida. Por exemplo, você pode usar CASE em instruções, como SELECT, UPDATE, DELETE e SET, e em cláusulas, como<select_list>, IN, WHERE, ORDER BY e HAVING.G.

Convenções de sintaxe de Transact-SQL

Sintaxe

Sintaxe do SQL Server, Banco de Dados SQL do Azure e Azure Synapse Analytics.

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

Sintaxe do Parallel Data Warehouse.

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

Observação

Para ver a sintaxe do Transact-SQL para o SQL Server 2014 e versões anteriores, confira a Documentação das versões anteriores.

Argumentos

input_expression

A expressão avaliada quando o formato CASE simples é usado. input_expression é qualquer expression válida.

WHEN when_expression

Uma expressão simples com a qual input_expression é comparada quando o formato CASE simples é usado. when_expression é qualquer expressão válida. Os tipos de dados de input_expression e cada when_expression devem ser os mesmos ou devem ser uma conversão implícita.

THEN result_expression

A expressão retornada quando input_expression é igual a when_expression é avaliada como TRUE ou Boolean_expression é avaliada como TRUE. result expression é qualquer expression válida.

ELSE else_result_expression

A expressão retornada se nenhuma operação de comparação for avaliada como TRUE. Se esse argumento for omitido e nenhuma operação de comparação for avaliada como TRUE, CASE retornará NULL. else_result_expression é qualquer expressão válida. Os tipos de dados de else_result_expression e qualquer result_expression devem ser os mesmos ou devem ser uma conversão implícita.

WHEN Boolean_expression

A expressão booliana avaliada quando o formato CASE simples é usado. Boolean_expression é qualquer expressão booliana válida.

Observação

Para ver a sintaxe do Transact-SQL para o SQL Server 2014 e versões anteriores, confira a Documentação das versões anteriores.

Tipos de retorno

Retorna o tipo de precedência mais alto do conjunto de tipos em result_expressions e a else_result_expression opcional. Para obter mais informações, confira Precedência de tipo de dados (Transact-SQL).

Valores retornados

Expressão CASE simples:

A expressão CASE simples opera comparando a primeira expressão com a expressão em cada cláusula WHEN por equivalência. Se essas expressões forem equivalentes, a expressão na cláusula THEN será retornada.

  • Permite somente uma verificação de igualdade.

  • Na ordem especificada, avalia input_expression = when_expression para cada cláusula WHEN.

  • Retorna a result_expression da primeira input_expression = when_expression que é avaliada como TRUE.

  • Se nenhuma input_expression = when_expression for avaliada como TRUE, o Mecanismo de Banco de Dados do SQL Server retornará a else_result_expression, caso uma cláusula ELSE tenha sido especificada, ou um valor NULL, caso nenhuma cláusula ELSE tenha sido especificada.

Expressão CASE pesquisada:

  • Avalia, na ordem especificada, a Boolean_expression de cada cláusula WHEN.

  • Retorna result_expression da primeira Boolean_expression que é avaliada como TRUE.

  • Se nenhuma Boolean_expression for avaliada como TRUE, o Mecanismo de Banco de Dados retornará a else_result_expression, caso uma cláusula ELSE tenha sido especificada, ou um valor NULL, caso nenhuma cláusula ELSE tenha sido especificada.

Comentários

O SQL Server permite apenas 10 níveis de aninhamento em expressões CASE.

A expressão CASE não pode ser usada para controlar o fluxo de execução de instruções Transact-SQL, blocos de instruções, funções definidas pelo usuário e procedimentos armazenados. Para obter uma lista dos métodos de controle de fluxo, confira Linguagem de controle de fluxo (Transact-SQL).

A expressão CASE avalia suas condições em sequência e para com a primeira condição satisfatória. Em algumas situações, uma expressão é avaliada antes de uma expressãoCASE receber os resultados da expressão como sua entrada. É possível que haja erros na avaliação dessas expressões. As expressões agregadas que aparecem em argumentos WHEN de uma expressão CASE são avaliadas primeiro e, em seguida, são fornecidas para a expressão CASE. Por exemplo, a seguinte consulta gera um erro de divisão por zero ao gerar o valor da agregação MAX. Isso ocorre antes da avaliação da expressão 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;
GO

Você deve depender somente da ordem de avaliação das condições WHEN para expressões escalares (incluindo subconsultas não correlacionadas que retornam escalares), e não para expressões agregadas.

Você também deve garantir que pelo menos uma das expressões nas cláusulas THEN ou ELSE não seja a constante NULL. Embora NULL possa ser retornado de várias expressões de resultado, nem todas elas podem ser explicitamente a constante NULL. Se todas as expressões de resultado usarem a constante NULL, o erro 8133 será retornado.

Exemplos

a. Usar uma instrução SELECT com uma expressão CASE simples

Dentro de uma instrução SELECT, uma expressão CASE simples é permitida somente para uma verificação de igualdade; nenhuma outra comparação é feita. O exemplo a seguir usa a expressão CASE para alterar a exibição de categorias de linhas de produto para torná-las mais compreensíveis.

USE AdventureWorks2019;
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. Usar uma instrução SELECT com uma expressão CASE pesquisada

Dentro de uma instrução SELECT, a expressão CASE pesquisada é permitida para valores a serem substituídos no conjunto de resultados com base nos valores de comparação. O exemplo a seguir exibe o preço da lista como um comentário de texto com base na faixa de preços de um produto.

USE AdventureWorks2019;
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. Usar CASE em uma cláusula ORDER BY

O exemplo a seguir usa a expressão CASE em uma cláusula ORDER BY para determinar a ordem de classificação das linhas com base no valor da coluna fornecido. No primeiro exemplo, é avaliado o valor da coluna SalariedFlag da tabela HumanResources.Employee. Funcionários que têm o SalariedFlag definido como 1 são retornados pelo BusinessEntityID em ordem decrescente. Funcionários que têm o SalariedFlag definido como 0 são retornados pelo BusinessEntityID em ordem crescente. No segundo exemplo, o conjunto de resultados será ordenado pela coluna TerritoryName quando a coluna CountryRegionName for igual a 'United States' e por CountryRegionName para todas as outras linhas.

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

D. Usar CASE em uma instrução UPDATE

O exemplo a seguir usa a expressão CASE em uma instrução UPDATE para determinar o valor definido para a coluna VacationHours para funcionários com SalariedFlag definido como 0. Ao subtrair 10 horas dos resultados de VacationHours em um valor negativo, VacationHours é aumentado em 40 horas; caso contrário, VacationHours é aumentado em 20 horas. A cláusula OUTPUT é usada para exibir os valores de antes e depois das férias.

USE AdventureWorks2019;
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;
GO

E. Usar CASE em uma instrução SET

O exemplo a seguir usa a expressão CASE em uma instrução SET na função com valor de tabela dbo.GetContactInfo. No banco de dados AdventureWorks2019, todo os dados relacionados com pessoas são armazenados na tabela Person.Person. Por exemplo, a pessoa pode ser um funcionário, um representante de fornecedor ou um cliente. A função retorna o nome e o sobrenome de um determinado BusinessEntityID e o tipo de contato dessa pessoa. A expressão CASE na instrução SET determina o valor a ser exibido para a coluna ContactType com base na existência da coluna BusinessEntityID nas tabelas Employee, Vendor ou Customer.

USE AdventureWorks2019;
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);
GO

F. Usar CASE em uma cláusula HAVING

O exemplo a seguir usa a expressão CASE em uma cláusula HAVING para restringir as linhas retornadas pela instrução SELECT. A instrução retorna a taxa horária máxima para cada cargo na tabela HumanResources.Employee. A cláusula HAVING restringe os títulos aos que são mantidos por funcionários assalariados com uma taxa de pagamento máxima maior que 40 dólares ou por funcionários não assalariados com uma taxa de pagamento máxima maior que 15 dólares.

USE AdventureWorks2019;
GO

SELECT JobTitle,
    MAX(ph1.Rate) AS MaximumRate
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeePayHistory AS ph1
    ON e.BusinessEntityID = ph1.BusinessEntityID
GROUP BY JobTitle
HAVING (
        MAX(CASE
                WHEN SalariedFlag = 1 THEN ph1.Rate
                ELSE NULL
                END) > 40.00
        OR MAX(CASE
                WHEN SalariedFlag = 0 THEN ph1.Rate
                ELSE NULL
                END) > 15.00
        )
ORDER BY MaximumRate DESC;
GO

Exemplos: Azure Synapse Analytics e PDW (Analytics Platform System)

G. Usar uma instrução SELECT com uma expressão CASE

Dentro de uma instrução SELECT, a expressão CASEpermite que os valores sejam substituídos no conjunto de resultados com base nos valores de comparação. O exemplo a seguir usa a expressão CASE para alterar a exibição de categorias de linhas de produto para torná-las mais compreensíveis. Quando um valor não existe, o texto "Não destinado à venda" é exibido.

-- Uses AdventureWorks

SELECT ProductAlternateKey,
    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,
    EnglishProductName
FROM dbo.DimProduct
ORDER BY ProductKey;
GO

H. Usar CASE em uma instrução UPDATE

O exemplo a seguir usa a expressão CASE em uma instrução UPDATE para determinar o valor definido para a coluna VacationHours para funcionários com SalariedFlag definido como 0. Ao subtrair 10 horas dos resultados de VacationHours em um valor negativo, VacationHours é aumentado em 40 horas; caso contrário, VacationHours é aumentado em 20 horas.

-- Uses AdventureWorks

UPDATE dbo.DimEmployee
SET VacationHours = (
        CASE
            WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40
            ELSE (VacationHours + 20.00)
            END
        )
WHERE SalariedFlag = 0;
GO

Confira também