CASE (Transact-SQL)

Wertet eine Liste von Bedingungen aus und gibt einen von mehreren möglichen Ergebnisausdrücken zurück.

Der CASE-Ausdruck verfügt über zwei Formate:

  • Der einfache CASE-Ausdruck vergleicht einen Ausdruck mit mehreren einfachen Ausdrücken, um das Ergebnis festzulegen.

  • Der komplexe CASE-Ausdruck wertet eine Menge boolescher Ausdrücke aus, um das Ergebnis festzulegen.

Bei beiden Formaten wird ein optionales ELSE-Argument unterstützt.

CASE kann in jeder Anweisung oder Klausel verwendet werden, die einen gültigen Ausdruck zulässt. Sie können CASE beispielsweise in Anweisungen wie SELECT, UPDATE, DELETE und SET und in Klauseln wie select_list, IN, WHERE, ORDER BY und HAVING verwenden.

Themenlink (Symbol) Transact-SQL-Syntaxkonventionen


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

input_expression

Der Ausdruck, der ausgewertet wird, wenn das einfache CASE-Format verwendet wird. input_expression kann jeder gültige Ausdruck sein.

WHEN when_expression

Ein einfacher Ausdruck, mit dem input_expression verglichen wird, wenn das einfache CASE-Format verwendet wird. when_expression kann jeder gültige Ausdruck sein. Die Datentypen von input_expression und allen when_expression-Ausdrücken müssen gleich sein, oder es muss eine implizite Konvertierung vorliegen.

THEN result_expression

Der Ausdruck, der zurückgegeben wird, wenn input_expression gleich when_expression zu TRUE ausgewertet wird oder wenn Boolean_expression zu TRUE ausgewertet wird. result expression kann jeder gültige Ausdruck sein.

ELSE else_result_expression

Der Ausdruck, der zurückgegeben wird, wenn keine Vergleichsoperation TRUE ergibt. Wenn dieses Argument nicht angegeben und keine Vergleichsoperation zu TRUE ausgewertet wird, gibt die CASE-Funktion NULL zurück. else_result_expression kann jeder gültige Ausdruck sein. Die Datentypen von else_result_expression und allen result_expression-Ausdrücken müssen gleich sein, oder es muss eine implizite Konvertierung vorliegen.

WHEN Boolean_expression

Der boolesche Ausdruck, der bei Verwendung des komplexen CASE-Formats ausgewertet wird. Boolean_expression kann jeder gültige boolesche Ausdruck sein.

Gibt den vorrangigsten Typ der Typen in result_expressions und den optionalen else_result_expression-Ausdruck zurück. Weitere Informationen finden Sie unter Rangfolge der Datentypen (Transact-SQL).

Rückgabewerte

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.

In SQL Server ist für CASE-Ausdrücke nur eine Schachtelung von zehn Ebenen zulässig.

Der CASE-Ausdruck kann nicht zur Steuerung des Ausführungsflusses von Transact-SQL-Anweisungen, Anweisungsblöcken, benutzerdefinierten Funktionen und gespeicherten Prozeduren verwendet werden. Eine Liste der Methoden zur Ablaufsteuerung finden Sie unter Ablaufsteuerungssprache (Transact-SQL).

A. Verwenden einer SELECT-Anweisung mit einem einfachen CASE-Ausdruck

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


B. Verwenden einer SELECT-Anweisung mit einem komplexen CASE-Ausdruck

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


C. Verwenden von CASE als Ersatz für die IIf-Funktion von Microsoft Access

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;


D. Verwenden von CASE in einer ORDER BY-Klausel

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;


E. Verwenden von CASE in einer UPDATE-Anweisung

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; 


F. Verwenden von CASE in einer SET-Anweisung

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


G. Verwenden von CASE in einer HAVING-Klausel

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;


Community-Beiträge

HINZUFÜGEN
Anzeigen: