Can You Add An Or Statement To Swtich Case

Case (Transact-SQL)

Article
11/30/2021
9 minutes to read

Applies to: SQL Server (all supported versions) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform Organisation (PDW)

Evaluates a list of conditions and returns one of multiple possible result expressions.

The Example expression has ii formats:

The uncomplicated CASE expression compares an expression to a gear up of simple expressions to make up one's mind the result.
The searched CASE expression evaluates a set up of Boolean expressions to decide the result.

Both formats support an optional ELSE argument.

Case can exist used in any statement or clause that allows a valid expression. For case, you can utilise CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER Past, and HAVING.

Topic link icon Transact-SQL Syntax Conventions

Syntax

              -- Syntax for SQL Server, Azure SQL Database and Azure Synapse Analytics    --Uncomplicated Example expression:    CASE input_expression         WHEN when_expression THEN result_expression [ ...n ]         [ ELSE else_result_expression ]    END     --Searched CASE expression:   Instance        WHEN Boolean_expression THEN result_expression [ ...north ]         [ ELSE else_result_expression ]    END

              -- Syntax for Parallel Data Warehouse      CASE        WHEN when_expression And so result_expression [ ...northward ]         [ ELSE else_result_expression ]    Stop

Arguments

input_expression
Is the expression evaluated when the simple CASE format is used. input_expression is any valid expression.

WHEN when_expression
Is a simple expression to which input_expression is compared when the simple Example format is used. when_expression is whatsoever valid expression. The data types of input_expression and each when_expression must exist the same or must be an implicit conversion.

Then result_expression
Is the expression returned when input_expression equals when_expression evaluates to TRUE, or Boolean_expression evaluates to TRUE. result expression is any valid expression.

ELSE else_result_expression
Is the expression returned if no comparison operation evaluates to TRUE. If this argument is omitted and no comparing operation evaluates to TRUE, Instance returns Null. else_result_expression is whatsoever valid expression. The data types of else_result_expression and any result_expression must be the same or must be an implicit conversion.

WHEN Boolean_expression
Is the Boolean expression evaluated when using the searched CASE format. Boolean_expression is whatever valid Boolean expression.

Return Types

Returns the highest precedence blazon from the set of types in result_expressions and the optional else_result_expression. For more than information, see Data Type Precedence (Transact-SQL).

Render Values

Simple CASE expression:

The elementary CASE expression operates by comparing the first expression to the expression in each WHEN clause for equivalency. If these expressions are equivalent, the expression in the Then clause will be returned.

Allows only an equality check.
In the club specified, evaluates input_expression = when_expression for each WHEN clause.
Returns the result_expression of the kickoff input_expression = when_expression that evaluates to TRUE.
If no input_expression = when_expression evaluates to Truthful, the SQL Server Database Engine returns the else_result_expression if an ELSE clause is specified, or a Zippo value if no ELSE clause is specified.

Searched Instance expression:

Evaluates, in the social club specified, Boolean_expression for each WHEN clause.
Returns result_expression of the offset Boolean_expression that evaluates to TRUE.
If no Boolean_expression evaluates to Truthful, the Database Engine returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

Remarks

SQL Server allows for merely x levels of nesting in Example expressions.

The CASE expression cannot be used to control the menstruum of execution of Transact-SQL statements, statement blocks, user-divers functions, and stored procedures. For a listing of control-of-flow methods, see Command-of-Menstruum Language (Transact-SQL).

The Case expression evaluates its conditions sequentially and stops with the offset condition whose condition is satisfied. In some situations, an expression is evaluated earlier a Example expression receives the results of the expression every bit its input. Errors in evaluating these expressions are possible. Aggregate expressions that appear in WHEN arguments to a CASE expression are evaluated showtime, and so provided to the Case expression. For instance, the post-obit query produces a divide by goose egg error when producing the value of the MAX aggregate. This occurs prior to evaluating the Example expression.

              WITH Information (value) AS    (    SELECT 0    UNION ALL    SELECT ane    )    SELECT       CASE          WHEN MIN(value) <= 0 And then 0          WHEN MAX(1/value) >= 100 And so 1       End    FROM Information ;

You should just depend on society of evaluation of the WHEN conditions for scalar expressions (including non-correlated sub-queries that render scalars), not for aggregate expressions.

Examples

A. Using a SELECT statement with a simple Example expression

Within a SELECT argument, a simple Example expression allows for only an equality check; no other comparisons are made. The post-obit example uses the Example expression to modify the display of product line categories to make them more understandable.

              USE AdventureWorks2012;   Become   SELECT   ProductNumber, Category =         CASE ProductLine            WHEN 'R' THEN 'Route'            WHEN 'G' And so 'Mountain'            WHEN 'T' And then 'Touring'            WHEN 'S' And so 'Other sale items'            ELSE 'Not for sale'         END,      Proper noun   FROM Production.Product   ORDER By ProductNumber;   GO

B. Using a SELECT statement with a searched Instance expression

Inside a SELECT statement, the searched Example expression allows for values to be replaced in the result gear up based on comparison values. The post-obit example displays the list price every bit a text annotate based on the price range for a product.

              USE AdventureWorks2012;   Get   SELECT   ProductNumber, Proper noun, "Cost Range" =          Instance             WHEN ListPrice =  0 Then 'Mfg item - non for resale'            WHEN ListPrice < l THEN 'Nether $50'            WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Nether $250'            WHEN ListPrice >= 250 and ListPrice < 1000 And then 'Under $1000'            ELSE 'Over $g'         Cease   FROM Production.Product   ORDER By ProductNumber ;   GO

C. Using Example in an Society Past clause

The following examples uses the CASE expression in an ORDER BY clause to make up one's mind the sort order of the rows based on a given column value. In the commencement example, the value in the SalariedFlag column of the HumanResources.Employee table is evaluated. Employees that take the SalariedFlag set to 1 are returned in order by the BusinessEntityID in descending order. Employees that have the SalariedFlag set to 0 are returned in order past the BusinessEntityID in ascending order. In the 2nd example, the event set is ordered by the column TerritoryName when the column CountryRegionName is equal to 'The states' and by CountryRegionName for all other rows.

              SELECT BusinessEntityID, SalariedFlag   FROM HumanResources.Employee   ORDER By CASE SalariedFlag WHEN one THEN BusinessEntityID Finish DESC           ,Example WHEN SalariedFlag = 0 THEN BusinessEntityID END;   Get

              SELECT BusinessEntityID, LastName, TerritoryName, CountryRegionName   FROM Sales.vSalesPerson   WHERE TerritoryName IS NOT Nothing   ORDER By Instance CountryRegionName WHEN 'Usa' Then TerritoryName            ELSE CountryRegionName END;

D. Using Instance in an UPDATE statement

The post-obit instance uses the Example expression in an UPDATE statement to determine the value that is gear up for the column VacationHours for employees with SalariedFlag ready to 0. When subtracting 10 hours from VacationHours results in a negative value, VacationHours is increased by 40 hours; otherwise, VacationHours is increased by 20 hours. The OUTPUT clause is used to brandish the earlier and after vacation values.

              USE AdventureWorks2012;   Become   UPDATE HumanResources.Employee   SET VacationHours =        ( CASE            WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + twoscore            ELSE (VacationHours + 20.00)          END       )   OUTPUT Deleted.BusinessEntityID, Deleted.VacationHours AS BeforeValue,           Inserted.VacationHours AS AfterValue   WHERE SalariedFlag = 0;

E. Using Case in a Fix argument

The following case uses the CASE expression in a Prepare statement in the table-valued function dbo.GetContactInfo. In the AdventureWorks2012 database, all information related to people is stored in the Person.Person table. For instance, the person may be an employee, vendor representative, or a client. The function returns the get-go and last name of a given BusinessEntityID and the contact type for that person.The CASE expression in the SET statement determines the value to display for the column ContactType based on the existence of the BusinessEntityID column in the Employee, Vendor, or Client tables.

              USE AdventureWorks2012;   Go   CREATE FUNCTION dbo.GetContactInformation(@BusinessEntityID INT)       RETURNS @retContactInformation TABLE    (   BusinessEntityID INT Not Nil,   FirstName NVARCHAR(50) NULL,   LastName NVARCHAR(50) NULL,   ContactType NVARCHAR(50) Nothing,       Principal Key Clustered (BusinessEntityID ASC)   )    AS    -- Returns the first name, last proper noun and contact blazon for the specified contact.   Brainstorm       DECLARE            @FirstName NVARCHAR(l),            @LastName NVARCHAR(50),            @ContactType NVARCHAR(50);          -- Become common contact information       SELECT            @BusinessEntityID = BusinessEntityID,    @FirstName = FirstName,            @LastName = LastName       FROM Person.Person        WHERE BusinessEntityID = @BusinessEntityID;          SET @ContactType =            Example                -- 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'                  -- Bank check for store               WHEN EXISTS(SELECT * FROM Purchasing.Vendor Equally five                             WHERE 5.BusinessEntityID = @BusinessEntityID)                    So 'Store Contact'                  -- Check for private consumer               WHEN EXISTS(SELECT * FROM Sales.Client AS c                    WHERE c.PersonID = @BusinessEntityID)                    THEN 'Consumer'           Stop;          -- Render the information to the caller       IF @BusinessEntityID IS Not NULL        BEGIN           INSERT @retContactInformation           SELECT @BusinessEntityID, @FirstName, @LastName, @ContactType;       Terminate;          Render;   END;   GO      SELECT BusinessEntityID, FirstName, LastName, ContactType   FROM dbo.GetContactInformation(2200);   GO   SELECT BusinessEntityID, FirstName, LastName, ContactType   FROM dbo.GetContactInformation(5);

F. Using Instance in a HAVING clause

The following example uses the CASE expression in a HAVING clause to restrict the rows returned by the SELECT statement. The argument returns the maximum hourly charge per unit for each job championship in the HumanResources.Employee table. The HAVING clause restricts the titles to those that are held by men with a maximum pay rate greater than 40 dollars or women with a maximum pay rate greater than 42 dollars.

              USE AdventureWorks2012;   Become   SELECT JobTitle, MAX(ph1.Rate)Every bit MaximumRate   FROM HumanResources.Employee AS due east   JOIN HumanResources.EmployeePayHistory As ph1 ON due east.BusinessEntityID = ph1.BusinessEntityID   Group BY JobTitle   HAVING (MAX(Case WHEN Gender = 'M'            And so ph1.Rate            ELSE NULL Finish) > xl.00        OR MAX(CASE WHEN Gender  = 'F'            THEN ph1.Rate             ELSE NULL Terminate) > 42.00)   Lodge By MaximumRate DESC;

Examples: Azure Synapse Analytics and Analytics Platform System (PDW)

G. Using a SELECT statement with a Example expression

Inside a SELECT statement, the CASE expression allows for values to be replaced in the upshot prepare based on comparison values. The following example uses the CASE expression to change the display of production line categories to make them more understandable. When a value does not exist, the text "Not for sale' is displayed.

              -- Uses AdventureWorks      SELECT   ProductAlternateKey, Category =         Instance ProductLine            WHEN 'R' THEN 'Road'            WHEN 'Thou' So 'Mountain'            WHEN 'T' Then 'Touring'            WHEN 'S' Then 'Other sale items'            ELSE 'Not for sale'         End,      EnglishProductName   FROM dbo.DimProduct   ORDER Past ProductKey;

H. Using Case in an UPDATE statement

The following example uses the Case expression in an UPDATE argument to determine the value that is set for the column VacationHours for employees with SalariedFlag set to 0. When subtracting 10 hours from VacationHours results in a negative value, VacationHours is increased by xl hours; otherwise, VacationHours is increased by xx hours.

              -- Uses AdventureWorks       UPDATE dbo.DimEmployee   SET VacationHours =        ( Case            WHEN ((VacationHours - 10.00) < 0) And so VacationHours + 40            ELSE (VacationHours + 20.00)           END       )    WHERE SalariedFlag = 0;