Sql server® 2012 t-sql fundamentals

 

CHAPTER 2

 

Single-Table Queries

 

 53

Using parentheses to force precedence with logical operators is similar to using parentheses with 

arithmetic operators. For example, without parentheses in the following expression, multiplication 

precedes addition.

SELECT 10 + 2 * 3;

Therefore, this expression returns 16. You can use parentheses to force the addition to be calcu-

lated first.

SELECT (10 + 2) * 3;

This time, the expression returns 36.

CASE Expressions

A CASE expression is a scalar expression that returns a value based on conditional logic. Note that 

CASE is an expression and not a statement; that is, it doesn’t let you control flow of activity or do 

something based on conditional logic. Instead, the value it returns is based on conditional logic. Be-

cause CASE is a scalar expression, it is allowed wherever scalar expressions are allowed, such as in the 

SELECT, WHERE, HAVING, and ORDER BY clauses and in CHECK constraints.

The two forms of CASE expression are simple and searched. The simple form allows you to compare 

one value or scalar expression with a list of possible values and return a value for the first match. If no 

value in the list is equal to the tested value, the CASE expression returns the value that appears in the 

ELSE clause (if one exists). If a CASE expression doesn’t have an ELSE clause, it defaults to ELSE NULL.

For example, the following query against the Production.Products table uses a CASE expression in 

the SELECT clause to produce the description of the categoryid column value.

SELECT productid, productname, categoryid, 

  CASE categoryid 

    WHEN 1 THEN 'Beverages' 

    WHEN 2 THEN 'Condiments' 

    WHEN 3 THEN 'Confections' 

    WHEN 4 THEN 'Dairy Products' 

    WHEN 5 THEN 'Grains/Cereals' 

    WHEN 6 THEN 'Meat/Poultry' 

    WHEN 7 THEN 'Produce' 

    WHEN 8 THEN 'Seafood' 

    ELSE 'Unknown Category' 

  END AS categoryname 

FROM Production.Products;

www.it-ebooks.info

---

54  

Microsoft SQL Server 2012 T-SQL Fundamentals

This query produces the following output, shown in abbreviated form.

productid   productname         categoryid  categoryname 

----------- ------------------- ----------- ---------------- 

1           Product HHYDP       1           Beverages 

2           Product RECZE       1           Beverages 

3           Product IMEHJ       2           Condiments 

4           Product KSBRM       2           Condiments 

5           Product EPEIM       2           Condiments 

6           Product VAIIV       2           Condiments 

7           Product HMLNI       7           Produce 

8           Product WVJFP       2           Condiments 

9           Product AOZBW       6           Meat/Poultry 

10          Product YHXGE       8           Seafood 

... 

 

(77 row(s) affected)

The preceding query is a simple example of using the CASE expression. Unless the set of catego-

ries is very small and static, your best design choice is probably to maintain (for example) the product 

categories in a table, and join that table with the Products table when you need to get the category 

descriptions. In fact, the TSQL2012 database has just such a Categories table.

The simple CASE form has a single test value or expression right after the CASE keyword that is 

compared with a list of possible values in the WHEN clauses. The searched CASE form is more flexible 

because it allows you to specify predicates, or logical expressions, in the WHEN clauses rather than 

restricting you to equality comparisons. The searched CASE expression returns the value in the THEN 

clause that is associated with the first WHEN logical expression that evaluates to TRUE. If none of 

the WHEN expressions evaluates to TRUE, the CASE expression returns the value that appears in the 

ELSE clause (or NULL if an ELSE clause is not specified). For example, the following query produces a 

value category description based on whether the value is less than 1,000.00, between 1,000.00 and 

3,000.00, or greater than 3,000.00.

SELECT orderid, custid, val, 

  CASE  

    WHEN val < 1000.00                   THEN 'Less than 1000' 

    WHEN val BETWEEN 1000.00 AND 3000.00 THEN 'Between 1000 and 3000' 

    WHEN val > 3000.00                   THEN 'More than 3000' 

    ELSE 'Unknown' 

  END AS valuecategory 

FROM Sales.OrderValues;

This query generates the following output.

orderid     custid      val      valuecategory 

----------- ----------- -------- ---------------------- 

10248       85          440.00   Less than 1000 

10249       79          1863.40  Between 1000 and 3000 

10250       34          1552.60  Between 1000 and 3000 

10251       84          654.06   Less than 1000 

10252       76          3597.90  More than 3000 

10253       34          1444.80  Between 1000 and 3000 

10254       14          556.62   Less than 1000 

www.it-ebooks.info

---