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;