CASE (Transact-SQL)
Avalia uma lista de condições e retorna uma das várias expressões de resultados 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 booleanas 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.
.gif "Ícone de vínculo de tópico")
Sintaxe
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
Argumentos
input_expression
É a expressão avaliada quando o formato CASE simples é usado. input_expression é qualquer expressão válida.WHEN when_expression
É uma expressão simples à 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 iguais ou 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 expressão 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 iguais ou ser uma conversão implícita.WHEN Boolean_expression
É a expressão booleana avaliada ao usar o formato CASE pesquisado. Boolean_expression é qualquer expressão booleana válida.
Tipos de retorno
Retorna o tipo de precedência mais alto do conjunto de tipos em result_expressions e na else_result_expressionopcional. Para obter mais informações, consulte Precedência de tipo de dados (Transact-SQL).
Valores de retorno
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.
Avalia input_expression e, na ordem especificada, avalia input_expression = when_expression para cada cláusula WHEN.
Retorna a result_expression da primeira input_expression = when_expression 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, se uma cláusula ELSE for especificada, ou um valor NULL, se nenhuma cláusula ELSE for especificada.
Expressão CASE pesquisada:
Na ordem especificada, avalia Boolean_expression para cada cláusula WHEN.
Retorna a result_expression da primeira Boolean_expression avaliada como TRUE.
Se nenhuma Boolean_expression for avaliada como TRUE, o Mecanismo de Banco de Dados retornará a else_result_expression, se uma cláusula ELSE for especificada, ou um valor NULL, se nenhuma cláusula ELSE for 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, consulte Linguagem de controle de fluxo (Transact-SQL).
A instrução CASE avalia suas condições sequencialmente e para com a primeira condição satisfatória. Em alguns casos, uma expressão é avaliada antes de uma instrução CASE receba os resultados da expressão como sua entrada. Erros na avaliação dessas expressões são possíveis. As expressões agregadas que aparecem em argumentos WHEN em uma instrução CASE são avaliadas primeiro, depois fornecidas para a instrução CASE. Por exemplo, a consulta a seguir gera um erro de divisão por zero ao produzir o valor da expressão agregada MAX. Isso ocorre antes de avaliar a 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 ;
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.
Exemplos
A. Usando 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 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
B. Usando 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 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
C. Usando CASE para substituir a função IIf usada no Microsoft Access
CASE fornece uma funcionalidade semelhante à função IIf no Microsoft Access. O exemplo a seguir mostra uma consulta simples que usa IIf para fornecer um valor de saída para a coluna TelephoneInstructions em uma tabela do Access denominada db1.ContactInfo.
SELECT FirstName, LastName, TelephoneNumber,
IIf(IsNull(TelephoneInstructions),"Any time",
TelephoneInstructions) AS [When to Contact]
FROM db1.ContactInfo;
O exemplo a seguir usa CASE para fornecer um valor de saída para a coluna TelephoneSpecialInstructions na exibição do 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;
D. Usando 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, o valor da coluna SalariedFlag da tabelaHumanResources.Employee é avaliado. Funcionários que têm o SalariedFlag definido como 1 são retornados pelo EmployeeID em ordem decrescente. Funcionários que têm o SalariedFlag definido como 0 são retornados pelo EmployeeID em ordem crescente. No segundo exemplo, o conjunto de resultados é 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;
E. Usando 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 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;
F. Usando 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 AdventureWorks2008R2, 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 daquela 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 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);
G. Usando 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 homens com uma taxa de pagamento máxima maior que 40 dólares ou por mulheres com uma taxa de pagamento máxima maior que 42 dólares.
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;
Consulte também