CASE (Transact-SQL)

Einfacher CASE-Ausdruck:

Der einfache CASE-Ausdruck überprüft den ersten Ausdruck mit dem Ausdruck in jeder WHEN-Klausel auf Übereinstimmungen. Wenn diese Ausdrücke gleichwertig sind, wird der Ausdruck in der THEN-Klausel zurückgegeben.

• Ermöglicht nur eine Überprüfung auf Gleichheit.

• Wertet input_expression aus, und wertet anschließend in der angegebenen Reihenfolge die input_expression = when_expression-Ausdrücke für jede WHEN-Klausel aus.

• Gibt den result_expression-Ausdruck des ersten input_expression = when_expression-Ausdrucks zurück, der zu TRUE ausgewertet wird.

• Wenn kein input_expression = when_expression-Ausdruck zu TRUE ausgewertet wird, gibt SQL Server Database Engine (Datenbankmodul) den else_result_expression-Ausdruck zurück, falls eine ELSE-Klausel angegeben ist, oder einen NULL-Wert, falls keine ELSE-Klausel angegeben ist.

Komplexer CASE-Ausdruck:

• Für jede WHEN-Klausel werden die Boolean_expression-Ausdrücke in der angegebenen Reihenfolge ausgewertet.

• Gibt den result_expression-Ausdruck des ersten Boolean_expression-Ausdrucks zurück, der zu TRUE ausgewertet wird.

• Wird kein Boolean_expression-Ausdruck zu TRUE ausgewertet, gibt Database Engine (Datenbankmodul) den else_result_expression-Ausdruck zurück, falls eine ELSE-Klausel angegeben ist, oder einen NULL-Wert, falls keine ELSE-Klausel angegeben ist.

Innerhalb einer SELECT-Anweisung ermöglicht ein einfacher CASE-Ausdruck nur eine Überprüfung auf Gleichheit. Andere Vergleiche werden nicht ausgeführt. Im folgenden Beispiel wird mithilfe eines CASE-Ausdrucks die Anzeige von Produktkategorien geändert, um das Verständnis zu erleichtern.

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

Innerhalb einer SELECT-Anweisung können mit dem komplexen CASE-Ausdruck Werte im Resultset basierend auf den Vergleichsergebnissen ersetzt werden. Im folgenden Beispiel wird anstelle des Listenpreises ein Kommentar angezeigt, der vom Preisbereich der einzelnen Produkte abhängt.

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

Die von CASE bereitgestellte Funktionalität entspricht der IIf-Funktion in Microsoft Access. Das folgende Beispiel zeigt eine einfache Abfrage, die mithilfe von IIf einen Ausgabewert für die TelephoneInstructions-Spalte in einer Access-Tabelle mit dem Namen db1.ContactInfo bereitstellt.

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

Im folgenden Beispiel wird mithilfe von CASE ein Ausgabewert für die TelephoneSpecialInstructions-Spalte in der AdventureWorks-Sicht von Person.vAdditionalContactInfo bereitgestellt.

USE AdventureWorks;
GO
SELECT FirstName, LastName, TelephoneNumber, 'When to Contact' = 
     CASE
          WHEN TelephoneSpecialInstructions IS NULL THEN 'Any time'
          ELSE TelephoneSpecialInstructions
     END
FROM Person.vAdditionalContactInfo;

Im den folgenden Beispielen wird der CASE-Ausdruck in einer ORDER BY-Klausel verwendet, um die Sortierreihenfolge der Zeilen auf Grundlage eines bestimmten Spaltenwerts festzulegen. Im ersten Beispiel wird der Wert in der SalariedFlag-Spalte der HumanResources.Employee-Tabelle ausgewertet. Mitarbeiter, für die das SalariedFlag auf 1 festgelegt ist, werden sortiert nach EmployeeID in absteigender Reihenfolge zurückgegeben. Mitarbeiter, für die das SalariedFlag auf 0 festgelegt ist, werden nach EmployeeID in aufsteigender Reihenfolge zurückgegeben. Im zweiten Beispiel wird das Resultset nach Spalte TerritoryName sortiert, wenn die Spalte CountryRegionName gleich "USA" ist, und für alle anderen Zeilen nach CountryRegionName.

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;

Im folgenden Beispiel wird der CASE-Ausdruck in einer UPDATE-Anweisung verwendet, um den für die Spalte VacationHours festzulegenden Wert für Mitarbeiter zu bestimmen, für die SalariedFlag auf 0 festgelegt ist. Wenn der Abzug von 10 Stunden von den VacationHours-Ergebnissen zu einem negativen Wert führt, wird VacationHours um 40 Stunden erhöht; andernfalls wird VacationHours um 20 Stunden erhöht. Die OUTPUT-Klausel wird zur Anzeige der Werte vor und nach dem Urlaub verwendet.

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; 

Im folgenden Beispiel wird der CASE-Ausdruck in einer SET-Anweisung in der Tabellenwertfunktion dbo.GetContactInfo verwendet. In der Datenbank AdventureWorks werden alle Personendaten in der Tabelle Person.Contact gespeichert. Die Person kann zum Beispiel ein Mitarbeiter, Herstellervertreter, Einzelhandel-Vertriebsmitarbeiter oder ein Endverbraucher sein. Die Funktion gibt den Vor- und Nachnamen einer bestimmten ContactID und des Kontakttyps für diese Person zurück. Der CASE-Ausdruck in der SET-Anweisung legt den Wert fest, der für die Spalte ContactType auf Grundlage der vorhandenen ContactID-Spalte in den (Endverbraucher)-Tabellen Employee, StoreContact, VendorContact oder Individual angezeigt wird.

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

Im folgenden Beispiel wird der CASE-Ausdruck in einer HAVING-Klausel verwendet, um die von der SELECT-Anweisung zurückgegebenen Zeilen einzuschränken. Die Anweisung gibt einen Wert für den maximalen Stundensatz jeder Berufsbezeichnung in der Tabelle HumanResources.Employee zurück. Die HAVING-Klausel schränkt Titel auf Männern mit einem maximalen Stundensatz von über 40 Dollar oder auf Frauen mit einem maximalen Stundensatz von über 42 Dollar ein.

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;