CASE (Transact-SQL)

  • Articolo

Data aggiornamento: 14 aprile 2006

Valuta un elenco di condizioni e restituisce una delle espressioni di risultato possibili.

L'espressione CASE ha due formati:

  • L'espressione CASE semplice esegue un confronto tra un'espressione e un set di espressioni semplici per determinare il risultato.
  • L'espressione CASE avanzata valuta un set di espressioni booleane per determinare il risultato.

Entrambi i formati supportano un argomento facoltativo ELSE.

È possibile utilizzare CASE in un'istruzione o una clausola in cui è consentita un'espressione valida. Ad esempio, è possibile utilizzare CASE nelle espressioni quali SELECT, UPDATE, DELETE e SET e nelle clausole quali select_list, IN, WHERE, ORDER BY e HAVING.

Icona di collegamento a un argomentoConvenzioni della sintassi Transact-SQL

Sintassi

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

Argomenti

  • input_expression
    Espressione valutata quando viene utilizzato il formato semplice della funzione CASE. input_expression è una qualsiasi espressione valida.
  • WHEN when_expression
    Espressione semplice con cui viene confrontato l'argomento input_expression quando viene utilizzato il formato semplice della funzione CASE. when_expression è una 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
    Espressione restituita quando il confronto di uguaglianza tra input_expression e when_expression restituisce TRUE oppure quando Boolean_expression restituisce TRUE. result expression è una qualsiasi espressione valida.
  • ELSE else_result_expression
    Espressione restituita quando nessuna operazione di confronto restituisce TRUE. Se questo argomento viene omesso e nessuna operazione di confronto restituisce TRUE, la funzione CASE restituisce NULL. else_result_expression è una 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 viene utilizzato il formato avanzato della funzione CASE. Boolean_expression è un'espressione booleana valida.

Tipi restituiti

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

Osservazioni

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

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

Valori restituiti

Espressione CASE semplice:

L'espressione CASE semplice esegue un confronto della prima espressione con l'espressione in ogni clausola WHERE per determinare l'equivalenza. Se le espressioni sono equivalenti, viene restituita l'espressione nella clausola THEN.

  • Consente di eseguire solo un controllo di uguaglianza.
  • Valuta input_expression, quindi nell'ordine specificato valuta il risultato di input_expression = when_expression per ogni clausola WHEN.
  • Restituisce l'argomento result_expression del primo confronto input_expression = when_expression che restituisce TRUE.
  • Se nessuna espressione 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, viene restituito un valore NULL.

Espressione CASE avanzata:

  • Valuta nell'ordine specificato l'argomento Boolean_expression per ogni clausola WHEN.
  • Restituisce l'argomento result_expression della prima espressione Boolean_expression che restituisce TRUE.
  • Se nessuna espressione Boolean_expression restituisce TRUE, il Motore di database restituisce else_result_expression se è stata specificata una clausola ELSE. In caso contrario, viene restituito un valore NULL.

Esempi

A. Utilizzo di 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 per renderle maggiormente comprensibili.

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. Utilizzo di un'istruzione SELECT con un'espressione CASE avanzata

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

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. Utilizzo di CASE in sostituzione della funzione IIf utilizzata in Microsoft Access

CASE offre funzionalità simili alla funzione IIf di Microsoft Access. Nell'esempio seguente viene illustrata una query semplice che utilizza IIf per ottenere un valore di output per la colonna TelephoneInstructions di una tabella di Access denominata db1.ContactInfo.

SELECT FirstName, LastName, TelephoneNumber, 
     IIf(IsNull(TelephoneInstructions),"Any time",
     TelephoneInstructions) AS [When to Contact]
FROM db1.ContactInfo;

Nell'esempio seguente viene utilizzata la funzione CASE per ottenere un valore di output per la colonna TelephoneSpecialInstructions della vista Person.vAdditionalContactInfo di AdventureWorks.

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

Nell'esempio seguente viene utilizzata l'espressione CASE in una clausola ORDER BY per stabilire l'ordinamento delle righe sulla base del valore della colonna SalariedFlag della tabella HumanResources.Employee. I dipendenti che hanno SalariedFlag impostato su 1 vengono restituiti in ordine di EmployeeID decrescente. I dipendenti che hanno SalariedFlag impostato su 0 vengono restituiti in ordine di EmployeeID crescente.

SELECT EmployeeID, SalariedFlag
FROM HumanResources.Employee
ORDER BY CASE SalariedFlag WHEN 1 THEN EmployeeID END DESC
        ,CASE WHEN SalariedFlag = 0 THEN EmployeeID END;
GO

E. Utilizzo di CASE in un'istruzione UPDATE

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

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;

E. Utilizzo di CASE in un'istruzione SET

Nell'esempio seguente viene utilizzata l'espressione CASE in un'espressione SET della funzione valutata a livello di tabella dbo.GetContactInfo. Nel database AdventureWorks, tutti i dati relativi alle persone sono memorizzati nella tabella Person.Contact. Ad esempio, la persona potrebbe essere un dipendente, un rappresentante di fornitori, un rappresentante di un punto vendita o un cliente. La funzione restituisce il nome e il cognome di un dato ContactID e il tipo di contatto per la persona. L'espressione CASE nell'istruzione SET determina il valore da visualizzare nella colonna ContactType sulla base dell'esistenza di ContactID nelle tabelle Employee, StoreContact, VendorContact o Individual (cliente).

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

D. Utilizzo di CASE in una clausola HAVING

Nell'esempio seguente viene utilizzata l'espressione CASE in una clausola HAVING per limitare le righe restituite dall'istruzione SELECT. L'istruzione restituisce la retribuzione oraria massima per ciascuna posizione della tabella HumanResources.Employee. La clausola HAVING limita la ricerca alle posizioni degli uomini con una retribuzione oraria massima superiore ai 40 dollari e delle donne con una retribuzione oraria massima superiore a 42 dollari.

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;

Vedere anche

Riferimento

Espressioni (Transact-SQL)
SELECT (Transact-SQL)
COALESCE (Transact-SQL)

Altre risorse

Utilizzo di CASE

Guida in linea e informazioni

Assistenza su SQL Server 2005

Cronologia modifiche

Versione Cronologia

14 aprile 2006
Nuovo contenuto:
  • Aggiunta di un esempio per illustrare come usufruire delle funzionalità di IIf utilizzando CASE.