CASE (Transact-SQL)

  • Articolo

Si applica a: SQL Server Database SQL di Azure Istanza gestita di SQL di Azure Azure Synapse Analytics Piattaforma di strumenti analitici (PDW) Endpoint di analisi SQL in Microsoft Fabric Warehouse in Microsoft Fabric

Valuta un elenco di condizioni e restituisce una tra più espressioni di risultato possibili.

L'espressione CASE ha due formati:

  • L'espressione semplice CASE confronta un'espressione con un set di espressioni semplici per determinare il risultato.

  • L'espressione cercata CASE valuta un set di espressioni booleane per determinare il risultato.

Entrambi i formati supportano un argomento facoltativo ELSE.

CASE può essere usato in qualsiasi istruzione o clausola che consenta un'espressione valida. Ad esempio, è possibile usare CASE nelle istruzioni come SELECT, UPDATE, DELETE e SET e nelle clausole come <select_list>, IN, WHERE, ORDER BY e HAVING.

Convenzioni relative alla sintassi Transact-SQL

Sintassi

Sintassi per SQL Server, database SQL di 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

Sintassi per Parallel Data Warehouse.

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

Argomenti

input_expression

Espressione valutata quando viene usato il formato semplice CASE . input_expression è qualsiasi espressione valida.

WHEN when_expression

Espressione semplice a cui input_expression viene confrontata quando viene usato il formato semplice CASE . when_expression è qualsiasi espressione valida. A input_expression e a ogni occorrenza di when_expression deve essere associato lo stesso tipo di dati o un tipo di dati convertibile in modo implicito.

THEN result_expression

L'espressione restituita quando input_expression è uguale a when_expression restituisce TRUE oppure Boolean_expression restituisce TRUE. result_expression è qualsiasi espressione valida.

ELSE else_result_expression

L'espressione restituita se nessuna operazione di confronto restituisce TRUE. Se questo argomento viene omesso e nessuna operazione di confronto restituisce TRUE, CASE restituisce NULL. else_result_expression è qualsiasi espressione valida. A else_result_expression e a ogni occorrenza di result_expression deve essere associato lo stesso tipo di dati o un tipo di dati convertibile in modo implicito.

WHEN Boolean_expression

Espressione booleana valutata quando si usa il formato di CASE ricerca. Boolean_expression è qualsiasi espressione booleana valida.

Tipi restituiti

Restituisce il tipo con precedenza maggiore nel set di tipi in result_expressions e l'argomento facoltativo else_result_expression. Per altre informazioni, vedere Precedenza dei tipi di dati (Transact-SQL).

Valori restituiti

Espressione CASE semplice:

L'espressione semplice CASE opera confrontando la prima espressione con l'espressione in ogni clausola WHEN per l'equivalenza. Se le espressioni sono equivalenti, viene restituita l'espressione nella clausola THEN.

  • Consente solo un controllo di uguaglianza.

  • Nell'ordine specificato, restituisce input_expression = when_expression per ogni clausola WHEN.

  • Restituisce l'argomento result_expression del primo confronto input_expression = when_expression che restituisce TRUE.

  • Se nessun confronto input_expression = when_expression restituisce TRUE, il motore di database di SQL Server restituisce else_result_expression se è stata specificata una clausola ELSE. In caso contrario, restituisce un valore NULL.

Espressione CASE avanzata:

  • Valuta nell'ordine specificato Boolean_expression per ogni clausola WHEN.

  • Restituisce result_expression del primo argomento Boolean_expression che restituisce TRUE.

  • Se nessun confronto Boolean_expression restituisce TRUE, il motore di database di SQL Server restituisce else_result_expression se è stata specificata una clausola ELSE. In caso contrario, restituisce un valore NULL.

Osservazioni:

SQL Server consente solo 10 livelli di annidamento nelle CASE espressioni.

L'espressione CASE non può essere usata per controllare il flusso di esecuzione di istruzioni Transact-SQL, blocchi di istruzioni, funzioni definite dall'utente e stored procedure. Per un elenco di metodi di controllo di flusso, vedere Elementi del linguaggio per il controllo di flusso (Transact-SQL).

L'espressione CASE valuta le condizioni in sequenza e si arresta con la prima condizione la cui condizione è soddisfatta. In alcune situazioni, un'espressione viene valutata prima che un'espressione CASE riceva i risultati dell'espressione come input. È possibile che si verifichino errori nella valutazione di queste espressioni. Le espressioni di aggregazione visualizzate negli argomenti WHEN per un'espressione CASE vengono valutate per prime, quindi fornite all'espressione CASE . Ad esempio, nella query seguente si verifica un errore di divisione per zero durante la generazione del valore dell'aggregazione MAX. Ciò si verifica prima della valutazione dell'espressione 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

È consigliabile dipendere solo dall'ordine di valutazione delle condizioni WHEN per le espressioni scalari (incluse sottoquery non correlate che restituiscono scalari), non per le espressioni di aggregazione.

È inoltre necessario assicurarsi che almeno una delle espressioni nelle clausole THEN o ELSE non sia la costante NULL. Anche se è possibile restituire NULL da più espressioni di risultato, non tutte queste espressioni possono essere esplicitamente la costante NULL. Se tutte le espressioni di risultato usano la costante NULL, viene restituito l'errore 8133.

Esempi

R. Usare un'istruzione SELECT con un'espressione CASE semplice

In un'istruzione SELECT un'espressione CASE semplice consente di eseguire solo un controllo di uguaglianza senza ulteriori confronti. Nell'esempio seguente viene utilizzata l'espressione CASE per modificare la visualizzazione delle categorie delle linee di prodotti in modo da renderle più intuitive.

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. Usare un'istruzione SELECT con un'espressione CASE ricercata

In un'istruzione SELECT l'espressione CASE avanzata consente di sostituire valori nel set di risultati in base ai valori di confronto. Nell'esempio seguente viene visualizzato il prezzo di listino come commento di testo in base alla fascia di prezzi per un prodotto.

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. Use CASE in una clausola ORDER BY

Negli esempi seguenti viene utilizzata l'espressione CASE in una clausola ORDER BY per determinare l'ordinamento delle righe in base a un determinato valore di colonna. Nel primo esempio, viene calcolato il valore nella colonna SalariedFlag della tabella HumanResources.Employee. I dipendenti per cui SalariedFlag è impostato su 1 vengono restituiti in ordine decrescente in base a BusinessEntityID. I dipendenti per cui SalariedFlag è impostato su 0 vengono restituiti in ordine crescente in base a BusinessEntityID. Nel secondo esempio il set di risultati viene ordinato in base alla colonna TerritoryName quando la colonna CountryRegionName è uguale a 'Stati Uniti' e in base a CountryRegionName per tutte le altre righe.

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. Use CASE in un'istruzione UPDATE

Nell'esempio seguente viene utilizzata l'espressione CASE in un'istruzione UPDATE per determinare il valore impostato per la colonna VacationHours per i dipendenti con SalariedFlag impostato su 0. Se sottraendo 10 ore da VacationHours viene restituito un valore negativo, VacationHours viene aumentato di 40 ore; in caso contrario, VacationHours viene aumentato di 20 ore. La clausola OUTPUT viene utilizzata per visualizzare i valori precedenti e successivi alle ferie.

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. Use CASE in un'istruzione SET

Nell'esempio seguente viene utilizzata l'espressione in un'istruzione CASE SET nella funzione dbo.GetContactInfocon valori di tabella . Nel database AdventureWorks2022 tutti i dati correlati alle persone vengono archiviati nella tabella Person.Person. La persona, ad esempio, può essere un dipendente, un rappresentante del fornitore o un cliente. La funzione restituisce il nome e il cognome di un dato BusinessEntityID e il tipo di contatto per tale persona. L'espressione CASE nell'istruzione SET determina il valore da visualizzare per la colonna ContactType in base all'esistenza della BusinessEntityID colonna nelle Employeetabelle , Vendoro 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. Use CASE in una clausola HAVING

Nell'esempio seguente viene usata l'espressione CASE in una clausola HAVING per limitare le righe restituite dall'istruzione SELECT. L'istruzione restituisce la tariffa oraria per ogni titolo di lavoro nella HumanResources.Employee tabella. La clausola HAVING limita i titoli a quelli detenuti dai dipendenti stipendiati con un tasso di retribuzione massimo maggiore di 40 dollari o dipendenti non retribuiti con un tasso di retribuzione massimo maggiore di 15 dollari.

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

Esempi: Azure Synapse Analytics e Piattaforma di strumenti analitici (PDW)

G. Usare un'istruzione SELECT con un'espressione CASE

All'interno di un'istruzione SELECT, l'espressione CASE consente di sostituire i valori nel set di risultati in base ai valori di confronto. Nell'esempio seguente viene utilizzata l'espressione CASE per modificare la visualizzazione delle categorie delle linee di prodotti in modo da renderle più intuitive. Quando non esiste un valore, viene visualizzato il testo "Non in vendita".

-- 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. Use CASE in un'istruzione UPDATE

Nell'esempio seguente viene utilizzata l'espressione CASE in un'istruzione UPDATE per determinare il valore impostato per la colonna VacationHours per i dipendenti con SalariedFlag impostato su 0. Se sottraendo 10 ore da VacationHours viene restituito un valore negativo, VacationHours viene aumentato di 40 ore; in caso contrario, VacationHours viene aumentato di 20 ore.

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

Vedi anche