Comparteix a través de

CASE (Transact-SQL)

  • Article

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) Punto de conexión de análisis SQL en Microsoft Fabric Almacenamiento en Microsoft Fabric

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.

Convenciones de sintaxis de Transact-SQL

Sintaxis

Sintaxis para SQL Server, Azure SQL Database y 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

Sintaxis para el almacenamiento de datos paralelo.

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

Argumentos

input_expression

La expresión evaluada cuando se utiliza el formato CASE sencillo. input_expression es cualquier expresión válida.

WHEN when_expression

Una expresión sencilla con la que se compara input_expression cuando se usa 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

La expresión devuelta cuando input_expression igual a 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

La expresión que se devuelve si ninguna operación de 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 cada result_expression deben ser iguales o deben ser una conversión implícita.

WHEN Boolean_expression

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, consulte Prioridad de tipo de datos (Transact-SQL).

Valores devueltos

Expresión CASE sencilla:

La expresión CASE sencilla 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 solo una comprobación de igualdad.

  • En el orden especificado, evalúa input_expression = when_expression por cada cláusula WHEN.

  • Devuelve la result_expression de la primera relación input_expression = when_expression que se evalúa como TRUE.

  • Si no hay ninguna relación input_expression = when_expression que se evalúe como TRUE, el Motor de base de datos de SQL Server devuelve la 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, la Boolean_expression de cada cláusula WHEN.

  • Devuelve la result_expression de la primera Boolean_expression que se evalúa como TRUE.

  • Si no hay ninguna Boolean_expressionque se evalúe como TRUE, el Motor de base de datosdevuelve la else_result_expression si se especifica una cláusula ELSE, o bien un valor NULL si no se especifica ninguna cláusula ELSE.

Observaciones

SQL Server solo 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, consulte Lenguaje de control de flujo (Transact-SQL).

La expresión CASE evalúa sus condiciones de forma secuencial y se detiene en la primera condición cuya condición se cumple. En algunas situaciones, se evalúa una expresión antes de que una expresión CASE reciba los resultados de la expresión como entrada. Los errores de evaluación de estas expresiones son posibles. Las expresiones de agregado que aparecen en los argumentos WHEN para una expresión CASE se evalúan primero y, después, se proporcionan a la expresión CASE. Por ejemplo, la siguiente consulta genera un error de división por cero al obtener el valor de agregado MAX. Esto ocurre antes de evaluar la expresión 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

Debe depender solo del orden de evaluación de las condiciones WHEN para las expresiones escalares (incluidas las subconsultas no correlacionadas que devuelven escalares), no para las expresiones de agregado.

También debe asegurarse de que al menos una de las expresiones de las cláusulas THEN o ELSE no sea la constante NULL. Aunque NULL se puede devolver desde varias expresiones de resultado, no todas pueden ser explícitamente la constante NULL. Si todas las expresiones de resultado usan la constante NULL, se devuelve el error 8133.

Ejemplos

A. Uso de una instrucción SELECT con una expresión CASE sencilla

En una instrucción SELECT, una expresión CASE sencilla solo 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 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. Uso de 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 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. Uso de 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 BusinessEntityID. Los empleados que tienen la columna SalariedFlag establecida en 0 se devuelven en orden ascendente según el BusinessEntityID. En el segundo ejemplo, el conjunto de resultados se ordena según la columna TerritoryName cuando la columna CountryRegionName es igual a 'Estados Unidos' y según la columna CountryRegionName en las demás filas.

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. Uso de CASE en una instrucción UPDATE

En el ejemplo siguiente se usa la expresión CASE en una instrucción UPDATE para determinar el valor 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 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. Uso de CASE en una instrucción SET

En el ejemplo siguiente se usa la expresión CASE en una instrucción SET en la función con valores de tabla dbo.GetContactInfo. En la base de datos AdventureWorks2022, todos los datos relacionados con las personas están almacenados en la tabla Person.Person. Por ejemplo, la persona puede ser un empleado, un representante de un proveedor o un consumidor. La función devuelve el nombre y el apellido de un determinado BusinessEntityID y el tipo de contacto de esa persona. La expresión CASE de la instrucción SET determina el valor que se muestra para la columna ContactType en función de la existencia de la columna BusinessEntityID en las tablas Employee, Vendor o 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. Uso de 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 para cada puesto en la tabla HumanResources.Employee. La cláusula HAVING restringe los títulos a aquellos que tienen los empleados asalariados con una tasa de pago máxima mayor que 40 dólares o empleados no asalariados con una tasa de pago máxima mayor 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

Ejemplos: Azure Synapse Analytics y Sistema de la plataforma de análisis (PDW)

G. Uso de una instrucción SELECT con una expresión CASE

En una instrucción SELECT, la expresión CASE permite sustituir valores en el conjunto de resultados basándose en los valores de comparación. 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. Cuando un valor no existe, aparece el texto “Not for sale”.

-- 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. Uso de CASE en una instrucción UPDATE

En el ejemplo siguiente se usa la expresión CASE en una instrucción UPDATE para determinar el valor 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.

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

Consulte también