CASE (Transact-SQL)

Espressione CASE semplice:

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

• Consente 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 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 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, restituisce un valore NULL.

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

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

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 l'espressione CASE per ottenere un valore di output per la colonna TelephoneSpecialInstructions nella 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;

Nell'esempio seguente viene utilizzata l'espressione CASE in una clausola ORDER BY per determinare l'ordinamento delle righe in base al valore di una 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 EmployeeID. I dipendenti per cui SalariedFlag è impostato su 0 vengono restituiti in ordine crescente in base a EmployeeID. 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 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;

Nell'esempio seguente viene utilizzata l'espressione CASE in un'istruzione UPDATE per determinare il valore impostato per la colonna VacationHours per i dipendenti per cui 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 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; 

Negli esempi seguenti viene utilizzata l'espressione CASE in un'istruzione SET nella funzione dbo.GetContactInfo con valori di tabella. Nel database AdventureWorks tutti i dati correlati alle persone vengono archiviati nella tabella Person.Contact. La persona, ad esempio, può essere un dipendente, un rappresentante del fornitore, un rappresentante del punto vendita al dettaglio o un cliente. La funzione restituisce il nome e il cognome di un ContactID specifico e il tipo di contatto per la persona. L'espressione CASE nell'istruzione SET determina il valore da visualizzare per la colonna ContactType in base alla presenza della colonna 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);

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 ogni titolo professionale nella tabella HumanResources.Employee. La clausola HAVING limita i titoli professionali a quelli associati a uomini con una retribuzione massima maggiore di 40 dollari o a donne con una retribuzione massima maggiore di 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;