CASE (Transact-SQL)
Aplica-se a:
SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse AnalyticsPDW (Analytics Platform System)Ponto de extremidade de SQL no Microsoft FabricWarehouse no Microsoft Fabric
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
CASEsimples compara uma expressão com um conjunto de expressões simples para determinar o resultado.A expressão
CASEpesquisada 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 exibir a sintaxe do Transact-SQL para o SQL Server 2014 (12.x) 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 exibir a sintaxe do Transact-SQL para o SQL Server 2014 (12.x) 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 AdventureWorks2022;
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 AdventureWorks2022;
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 AdventureWorks2022;
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 AdventureWorks2022, 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 AdventureWorks2022;
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 AdventureWorks2022;
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
Comentários
Em breve: Ao longo de 2024, eliminaremos os problemas do GitHub como o mecanismo de comentários para conteúdo e o substituiremos por um novo sistema de comentários. Para obter mais informações, consulte https://aka.ms/ContentUserFeedback.
Enviar e exibir comentários de