CASE (Transact-SQL)
Evalúa una lista de condiciones y devuelve una de las varias expresiones de resultado posibles.
La expresión CASE tiene dos formatos:
La expresión CASE sencilla compara una expresión con un conjunto de expresiones sencillas para determinar el resultado.
La expresión CASE buscada evalúa un conjunto de expresiones booleanas para determinar el resultado.
Ambos formatos admiten un argumento ELSE opcional.
CASE se puede utilizar en cualquier instrucción o cláusula que permite una expresión válida. Por ejemplo, puede utilizar CASE en instrucciones como SELECT, UPDATE, DELETE y SET, y en cláusulas como select_list, IN, WHERE, ORDER BY y HAVING.
Sintaxis
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
Es la expresión evaluada cuando se utiliza el formato CASE sencillo. input_expression es cualquier expresión válida.WHEN when_expression
Es una expresión sencilla con la que se compara input_expression cuando se utiliza el formato CASE sencillo. when_expression es cualquier expresión válida. Los tipos de datos de input_expression y cada when_expression deben ser iguales o deben ser una conversión implícita.THEN result_expression
Es la expresión devuelta cuando input_expression es igual que when_expression se evalúa como TRUE o Boolean_expression se evalúa como TRUE. result expression es cualquier expresión válida.ELSE else_result_expression
Es la expresión que se devuelve si ninguna comparación se evalúa como TRUE. Si se omite este argumento y ninguna comparación se evalúa como TRUE, CASE devuelve NULL. else_result_expression es cualquier expresión válida. Los tipos de datos de else_result_expression y cualquier result_expression deben ser iguales o deben ser una conversión implícita.WHEN Boolean_expression
Es la expresión booleana que se evalúa cuando se utiliza el formato CASE de búsqueda. Boolean_expression es cualquier expresión booleana válida.
Tipos de valores devueltos
Devuelve el tipo de prioridad más alto del conjunto de tipos de result_expressions y la expresión else_result_expression opcional. Para obtener más información, vea Prioridad de tipo de datos (Transact-SQL).
Valores devueltos
Expresión CASE simple:
La expresión CASE simple compara la primera expresión con la expresión de cada cláusula WHEN para determinar si se da alguna equivalencia. Si estas expresiones son equivalentes, se devolverá la expresión de la cláusula THEN.
Permite sólo una comprobación de igualdad.
Evalúa input_expression y, en el orden especificado, la expresión input_expression = when_expression para cada cláusula WHEN.
Devuelve result_expression de la primera input_expression = when_expression que se evalúa como TRUE.
Si input_expression = when_expression no se evalúa como TRUE, SQL Server Database Engine (Motor de base de datos de SQL Server) devuelve else_result_expression si se especifica una cláusula ELSE, o bien un valor NULL si no se especifica ninguna cláusula ELSE.
Expresión CASE buscada:
Evalúa, en el orden especificado, Boolean_expression para cada cláusula WHEN.
Devuelve result_expression de la primera Boolean_expression que se evalúa como TRUE.
Si Boolean_expression se evalúa como TRUE, Database Engine (Motor de base de datos) devuelve else_result_expression si se especifica una cláusula ELSE, o bien un valor NULL si no se especifica ninguna cláusula ELSE.
Notas
SQL Server sólo permite 10 niveles de anidamiento en las expresiones CASE.
La expresión CASE no se puede utilizar para controlar el flujo de ejecución de los bloques de instrucciones, funciones definidas por el usuario, procedimientos almacenados e instrucciones de Transact-SQL. Para obtener una lista de los métodos de control de flujo, vea Lenguaje de control de flujo (Transact-SQL).
Ejemplos
A. Usar una instrucción SELECT con una expresión CASE sencilla
En una instrucción SELECT, una expresión CASE sencilla sólo permite una comprobación de igualdad; no se pueden hacer otras comparaciones. En este ejemplo se utiliza la expresión CASE para cambiar la presentación de categorías de línea de productos con el fin de hacerla más comprensible.
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. Usar una instrucción SELECT con una expresión CASE de búsqueda
En una instrucción SELECT, la expresión CASE de búsqueda permite sustituir valores en el conjunto de resultados basándose en los valores de comparación. En el ejemplo siguiente se presenta el precio de venta como un comentario basado en el intervalo de precios de un producto.
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. Usar CASE para reemplazar la función IIf que se utiliza en Microsoft Access
CASE proporciona una funcionalidad similar a la función IIf de Microsoft Access. En el ejemplo siguiente se muestra una consulta sencilla que utiliza IIf para proporcionar un valor de salida para la columna TelephoneInstructions en una tabla de Access denominada db1.ContactInfo.
SELECT FirstName, LastName, TelephoneNumber,
IIf(IsNull(TelephoneInstructions),"Any time",
TelephoneInstructions) AS [When to Contact]
FROM db1.ContactInfo;
En el siguiente ejemplo se usa CASE para proporcionar un valor de salida para la columna TelephoneSpecialInstructions de la vista 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. Usar CASE en una cláusula ORDER BY
En los ejemplos siguientes se utiliza la expresión CASE en una cláusula ORDER BY para determinar el criterio de ordenación de las filas según el valor de una columna dada. En el primer ejemplo se evalúe el valor de la columna SalariedFlag de la tabla HumanResources.Employee. Los empleados que tienen la columna SalariedFlag establecida en 1 se devuelven en orden descendente según el EmployeeID. Los empleados que tienen la columna SalariedFlag establecida en 0 se devuelven en orden ascendente según el EmployeeID. En el segundo ejemplo, el conjunto de resultados se ordena según la columna TerritoryName cuando la columna CountryRegionName es igual a 'United States' y según la columna CountryRegionName en las demás filas.
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. Usar CASE en una instrucción UPDATE
En el ejemplo siguiente se utiliza la expresión CASE en una instrucción UPDATE para determinar el valor que está establecido en la columna VacationHours para los empleados con el valor de SalariedFlag establecido en 0. Al restar 10 horas de VacationHours da un valor negativo, VacationHours se incrementa en 40 horas; de lo contrario, VacationHours se incrementa en 20 horas. La cláusula OUTPUT se utiliza para mostrar los valores de las vacaciones antes y después.
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. Usar CASE en una instrucción SET
En el ejemplo siguiente se utiliza la expresión CASE en una instrucción SET en la función con valores de tabla dbo.GetContactInfo. En la base de datos AdventureWorks, todos los datos relacionados con las personas están almacenados en la tabla Person.Contact. Por ejemplo, la persona puede ser un empleado, un representante de un proveedor, un representante de la tienda o un consumidor.. La función devuelve el nombre y el apellido de un ContactID determinado y el tipo de contacto para esa persona. La expresión CASE en la instrucción SET determina el valor que se va a mostrar para la columna ContactType según la existencia de la columna ContactID en las tablas Employee, StoreContact, VendorContact o 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. Usar CASE en una cláusula HAVING
En el ejemplo siguiente se utiliza la expresión CASE en una cláusula HAVING para restringir las filas devueltas por la instrucción SELECT. La instrucción devuelve el precio por hora máximo para cada puesto en la tabla HumanResources.Employee. La cláusula HAVING restringe los títulos a aquellos que tienen los hombres con una tasa de pago máxima mayor que 40 dólares o las mujeres con una tasa de pago máxima mayor 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;
Vea también