CASE (Transact-SQL)

A. Using a SELECT statement with a case-simple expression
Using a SELECT statement with a simple case expression
In a SELECT statement, the case simple expression can only be used for equivalence checking, without making other comparisons. The following example uses a case expression to change the display of a product family category to make these categories easier to understand.

Use AdventureWorks2012;
GO
SELECT ProductNumber, Category =
Case ProductLine
When the ' R ' then ' Road '
When the ' M ' then ' Mountain '
When the ' T ' then ' Touring '
When ' S ' then ' Other sale items '
ELSE ' Not for sale '
END,
Name
From Production.Product
ORDER by ProductNumber;
GO

B. Using a SELECT statement with a case-search expression
In a SELECT statement, the case search expression allows values in the result set to be replaced by comparison values. The following example displays the list price as a text comment based on the product's pricing range.

Use AdventureWorks2012;  GO  SELECT   productnumber, Name, "price Range" = case when            ListPrice =  0 Then ' MFG item-not for Resale ' When           ListPrice < under $ ' when           ListPrice >= and ListPrice < ' under ' WHE           N ListPrice >= and ListPrice < "under $1000 '           ELSE ' over $1000 '        END from  production.produ CT  ORDER by ProductNumber;  GO  

C. Using case in the ORDER by clause
The following example uses a case expression in the ORDER BY clause to determine the sort order of rows based on the given column values.
The following examples uses the case expression in an ORDER BY clause to determine the sort ORDER of the rows based on a G Iven column value.
In the first example, the value of the HumanResources.Employee column in the Salariedflag table is computed. Employees with Salariedflag set to 1 will be returned in descending order by BusinessEntityID. Employees with Salariedflag set to 0 will be returned in ascending order by BusinessEntityID. In the second example, when the Territoryname column equals "states", the result set is sorted by the Countryregionname column and sorted by Countryregionname for all other rows.

SELECT BusinessEntityID, Salariedflag
From HumanResources.Employee
ORDER by Case Salariedflag if 1 then BusinessEntityID END DESC
, case is Salariedflag = 0 then BusinessEntityID END;
GO

D. Using case in an UPDATE statement
The following example uses a case expression in an UPDATE statement to determine the value set for the Salariedflag column of an employee who is set to 0 for VacationHours. If VacationHours minus 10 hours will get a negative value, vacationhours will increase by 40 hours, otherwise vacationhours will increase by 20 hours. The OUTPUT clause is used to display the before and after vacation time values.

Use AdventureWorks2012;
GO
UPDATE HumanResources.Employee
SET vacationhours =
(case
When ((VacationHours-10.00) < 0) then VacationHours + 40
ELSE (vacationhours + 20.00)
END
)
OUTPUT Deleted.businessentityid, deleted.vacationhours as Beforevalue,
Inserted.vacationhours as Aftervalue
WHERE salariedflag = 0;

E. Using a case in a SET statement
The following example is in the table-valued function dbo. The case expression is used in the SET statement in Getcontactinfo. In the AdventureWorks2012 database, all data related to a person is stored in the Person.person table. For example, a person might be an employee, a vendor representative, or a customer. The function returns the first and last name of the given BusinessEntityID and the user's contact type. The case expression in the SET statement determines the value of the column to be displayed contacttype based on whether there is a column in BusinessEntityID employee, Vendor, or Customer table.

Use AdventureWorks2012;
GO
CREATE FUNCTION dbo. Getcontactinformation (@BusinessEntityID int)
RETURNS @retContactInformation TABLE
(
BusinessEntityID int not NULL,
FirstName nvarchar () NULL,
LastName nvarchar () NULL,
ContactType nvarchar () NULL,
PRIMARY KEY CLUSTERED (BusinessEntityID ASC)
)
As
--Returns The first name, last name and contact type of the specified contact.
BEGIN
DECLARE
@FirstName nvarchar (50),
@LastName nvarchar (50),
@ContactType nvarchar (50);

--Get Common contact information
SELECT
@BusinessEntityID = BusinessEntityID,
@FirstName = FirstName,
@LastName = LastName
From Person.person
WHERE BusinessEntityID = @BusinessEntityID;

SET @ContactType =
Case
--Check for employee
When EXISTS (SELECT * from HumanResources.Employee as E
WHERE E.businessentityid = @BusinessEntityID)
Then ' Employee '

--Check for vendor
When EXISTS (SELECT * from Person.businessentitycontact as Bec
WHERE Bec. BusinessEntityID = @BusinessEntityID)
Then ' Vendor '

--Check for Store
When EXISTS (SELECT * from Purchasing.vendor as V
WHERE V.businessentityid = @BusinessEntityID)
Then ' Store Contact '

--Check for individual consumer
When EXISTS (SELECT * from Sales.Customer as C
WHERE C.personid = @BusinessEntityID)
Then ' Consumer '
END;

--Return The information to the caller
IF @BusinessEntityID is not NULL
BEGIN
INSERT @retContactInformation
SELECT @BusinessEntityID, @FirstName, @LastName, @ContactType;
END;

RETURN;
END;
GO

SELECT BusinessEntityID, FirstName, LastName, ContactType
FROM dbo. Getcontactinformation (2200);
GO
SELECT BusinessEntityID, FirstName, LastName, ContactType
FROM dbo. Getcontactinformation (5);

F. Use case in the HAVING clause
The following example uses a case expression in the HAVING clause to limit the rows returned by the SELECT statement. The statement returns the maximum per-hour rate for each job title in the HumanResources.Employee table. The HAVING clause restricts the position to two categories of employees: one is the highest hourly salary of more than $40 for male employees, and the other is the highest hourly salary of more than $42 for female employees.

Use AdventureWorks2012;  GO  SELECT jobtitle, MAX (PH1. Rate) as Maximumrate from  HumanResources.Employee as e  joins Humanresources.employeepayhistory as PH1 on E. BusinessEntityID = PH1. BusinessEntityID  GROUP by JobTitle have  (MAX (case is Gender = ' M ' then           ph1. Rate           ELSE NULL END) > 40.00       OR MAX if Gender  = ' F ' then           ph1. Rate            ELSE NULL END) > 42.00)  ORDER by Maximumrate DESC;  

G. SELECT statement using CASE expression
Using a SELECT statement with a case expression
The case expression in the SELECT statement allows you to compare value-based substitution values in the result set. The following example uses a case expression to change the displayed product line categories so that they are easier to understand. When a value does not exist, the text "cannot be displayed for sale."

--Uses AdventureWorks

SELECT Productalternatekey, Category =
Case ProductLine
When the ' R ' then ' Road '
When the ' M ' then ' Mountain '
When the ' T ' then ' Touring '
When ' S ' then ' Other sale items '
ELSE ' Not for sale '
END,
Englishproductname
FROM dbo. DimProduct
ORDER by ProductKey;

H. Using case in an UPDATE statement
The following example uses a case expression in an UPDATE statement to determine the value set for the Salariedflag column of an employee who is set to 0 for VacationHours. If VacationHours minus 10 hours will get a negative value, vacationhours will increase by 40 hours, otherwise vacationhours will increase by 20 hours.

--Uses AdventureWorks

UPDATE dbo. DimEmployee
SET vacationhours =
(case
When ((VacationHours-10.00) < 0) then VacationHours + 40
ELSE (vacationhours + 20.00)
END
)
WHERE salariedflag = 0;

Source: https://msdn.microsoft.com/zh-cn/library/ms181765.aspx

CASE (Transact-SQL)