CASE (Transact-SQL)
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 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 é permitido somente para 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).
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 AdventureWorks;
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 AdventureWorks;
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 AdventureWorks, Person.vAdditionalContactInfo.
USE AdventureWorks;
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
Os exemplos a seguir usam a expressão CASE em uma cláusula ORDER BY para determinar a ordem de classificação das linhas baseadas no valor de uma coluna fornecida. No primeiro exemplo, o valor da coluna SalariedFlag da tabela HumanResources.Employee é avaliado. Funcionários que têm o SalariedFlag definido como 1 são retornados ordenados por EmployeeID em ordem decrescente. Funcionários que têm o SalariedFlag definido como 0 são retornados ordenados por EmployeeID em ordem crescente. No segundo exemplo, o conjunto de resultados é ordenado pela coluna TerritoryName quando a coluna CountryRegionName é igual para a 'Estados Unidos' e por CountryRegionName para todas as outras linhas.
SELECT EmployeeID, SalariedFlag
FROM HumanResources.Employee
ORDER BY CASE SalariedFlag WHEN 1 THEN EmployeeID END DESC
,CASE WHEN SalariedFlag = 0 THEN EmployeeID END;
GO
SELECT SalesPersonID, 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 AdventureWorks;
GO
UPDATE HumanResources.Employee
SET VacationHours =
( CASE
WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40
ELSE (VacationHours + 20.00)
END
)
OUTPUT Deleted.EmployeeID, 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 AdventureWorks, todo os dados relacionados a pessoas são armazenados na tabela Person.Contact. Por exemplo, a pessoa pode ser um funcionário, representante de fornecedor, representante de loja de varejo ou um consumidor. A função retorna o primeiro e o último nome de um determinado ContactID 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 ContactID nas tabelas Employee, StoreContact, VendorContact ou Individual (consumidor).
USE AdventureWorks;
GO
CREATE FUNCTION dbo.GetContactInformation(@ContactID int)
RETURNS @retContactInformation TABLE
(
ContactID int NOT NULL,
FirstName nvarchar(50) NULL,
LastName nvarchar(50) NULL,
ContactType nvarchar(50) NULL,
PRIMARY KEY CLUSTERED (ContactID 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
@ContactID = ContactID,
@FirstName = FirstName,
@LastName = LastName
FROM Person.Contact
WHERE ContactID = @ContactID;
SET @ContactType =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee AS e
WHERE e.ContactID = @ContactID)
THEN 'Employee'
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact AS vc
INNER JOIN Person.ContactType AS ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN 'Vendor Contact'
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact AS sc
INNER JOIN Person.ContactType AS ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN 'Store Contact'
-- Check for individual consumer
WHEN EXISTS(SELECT * FROM Sales.Individual AS i
WHERE i.ContactID = @ContactID)
THEN 'Consumer'
END;
-- Return the information to the caller
IF @ContactID IS NOT NULL
BEGIN
INSERT @retContactInformation
SELECT @ContactID, @FirstName, @LastName, @ContactType;
END;
RETURN;
END;
GO
SELECT ContactID, FirstName, LastName, ContactType
FROM dbo.GetContactInformation(2200);
GO
SELECT ContactID, 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 AdventureWorks;
GO
SELECT Title, MAX(ph1.Rate)AS MaximumRate
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeePayHistory AS ph1 ON e.EmployeeID = ph1.EmployeeID
GROUP BY Title
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