Basics of T-sql: Beyond basic Level 6: Using case expressions and IIF functions

Basics of T-sql: Beyond basic Level 6: Using case expressions and IIF functions
Gregory larsen,2016/04/20 (First edition: 2014/04/09)

The series
This article is part of "Stairway series: The cornerstone of T-sql: Beyond the Basics"

From his stairway to T-SQL DML, Gregory Larsen covers more advanced aspects of the T-SQL language, such as subqueries.

In some cases, you need to write a single TSQL statement that can return different TSQL expressions based on the evaluation of another expression. When you need this functionality, you can use the case expression or the IIf feature to meet this requirement. In this article, I'll review the case and IIF syntax, and show you an example of the instance expression and the IIf function.

Understanding Case Expressions
The Transact-SQL CASE expression allows you to place conditional logic in the TSQL code. This conditional logic gives you a way to place different blocks of code in your TSQL statement based on the conditional logic's true or false evaluation. You can put multiple conditional expressions in a case expression. When you have more than one conditional expression in your case clause, the first expression that evaluates to True becomes the block of code that is computed by your TSQL statement. To better understand how the case expression works, I'll review the syntax of the cases expression and then pass some different examples.

Case-expression syntax
There are two different formats for case expressions: simple and searched. Each of these types has a slightly different format, as shown in 1.

Simple CASE expression:

Case input_expression
When when_expression then result_expression [... n]
[ELSE else_result_expression]
End

To search for a case expression:

Case
When Boolean expression then Result_expression [... n]
[ELSE else_result_expression]
End
Figure 1:case Expression syntax

By looking at two different formats for case expressions in Figure 1, you can see how each format provides a different way to identify one of several expressions that determine the result of a case expression. For both types of case, a Boolean test is performed for each when clause. Using a simple case expression, the left side of the Boolean test appears behind the word, called an input expression, and the right side of the Boolean test is immediately after when, called when expression. For a simple case expression, the operator between "input_expression" and "when_expression" is always equal to operator. In the case expression searched, each When clause will contain a "boolean_expression". This Boolean expression can be a simple Boolean expression with a single operator, or a complex Boolean expression with many different conditions. In addition, the found case expression can use a full set of Boolean operators.

Regardless of which case format is used, each when clause is compared in the order in which it appears. The result of the case expression will be based on the first when clause evaluated to true. If no when clause evaluates to true, an else expression is returned. When the ELSE clause is omitted, a null value is returned when the When clause evaluates to TRUE.

Sample data for the sample
To have a table demonstrate with a case expression, I'll use the script in Listing 1 to create a sample table named Myorder. If you want to follow my example and run them on your instance of SQL Server, you can create this table in the database of your choice.

CREATE TABLE MyOrder (
ID int identity,
Orderdt date,
Orderamt Decimal (10,2),
Layaway char (1));
Insert to Myorder value
(' 12-11-2012 ', 10.59,null),
(' 10-11-2012 ', 200.45, ' Y '),
(' 02-17-2014 ', 8.65,null),
(' 01-01-2014 ', 75.38,null),
(' 07-10-2013 ', 123.54,null),
(' 08-23-2009 ', 99.99,null),
(' 10-08-2013 ', 350.17, ' N '),
(' 04-05-2010 ', 180.76,null),
(' 03-27-2011 ', 1.49,null);
Listing 1: Creating a sample Table Myorder

Using a simple case expression in the when and else expressions
To demonstrate how the simple case expression format works, let me run the code in Listing 2.

SELECT year (Orderdt) as OrderYear,
Case year (Orderdt)
When 2014 so ' first year '
When 2013 then ' the second year '
When 2012 so ' 3 years '
ELSE "Year Four and beyond" year type
From MyOrder;
Listing 2: Simple CASE expression using the else expression

Let me start by talking about why this is a simple case expression. If you look at the code in Listing 2, you can see that the expression "year (Orderdt)" is specified after the case, and then I select three different when expressions from three different when expressions, starting with 2014. Because I specify the expression between the case and the first when keyword, this tells SQL Server that this is a simple case expression.

When my simple case expression is evaluated, it uses the equals operator ("=") between the "year (OrderDate)" value and the different when expressions. Therefore, the code in Listing 1 will show "Year 1" for the Yeartype column of the row with a Orderdt value of "2014", or Orderdt for "2013"?? Row displays "Year 2" that displays the line with the Orderdt years of "2012" for "Yearly 3". If the year of Orderdt does not match any of the When expressions, the Else condition displays "4th and later".

When I run the code in Listing 2, I get the output shown in result 1.

OrderYear Yeartype
----------- -----------------
2012 on 3
2012 on 3
2014 on 1
2014 on 1
2013 on 2
2009 4 years and beyond
2013 on 2
2010 4 years and beyond
2011 4 Years and beyond
Result 1: Run the results of Listing 2 o'clock

Use a simple case expression with no else expression
Let me run the code in Listing 3, which shows what happens when a simple case expression does not have an else clause.

SELECT year (Orderdt) as OrderYear,
Case year (Orderdt)
When 2014 so ' first year '
When 2013 then ' the second year '
When 2012 so ' Year 3 ' End year type
From MyOrder;
Listing 3: Simple CASE expression with no else clause

The code in Listing 3 is like the code in Listing 2, but there is no else clause. When I run the code in Listing 3, it produces the results shown in result 2.

OrderYear Yeartype
----------- --------
2012 on 3
2012 on 3
2014 on 1
2014 on 1
2013 on 2
NULL
2013 on 2
NULL
-NULL
Result 2: Run the results of Listing 3 o'clock

By looking at the output in result 2, you can see that the year of Orderdt in the Myorder table does not conform to the When clause condition, and SQL Server displays the row's Yeartype value as "NULL".

Using the search CASE expression
In a simple case expression, the when expression is evaluated based on the equality operator. By searching for a case expression, you can use other operators, with a slightly different case expression syntax. To demonstrate this, let's take a look at the code in Listing 4.

SELECT year (Orderdt) as OrderYear,
Case
That year (Orderdt) = 2014 so "first year"
Year (Orderdt) = Then ' 2 '
Year (Orderdt) = Then ' 3 years '
Year (Orderdt) <2012 then ' 4 years and beyond '
END as Yeartype
From MyOrder;
Listing 4: Searching the CASE expression

If you look at the code in Listing 4, you can see that when clause immediately follows the case clause, there is no text between the two clauses. This tells SQL Server the case expression for this search. Also notice the Boolean expression following each when clause. As you can see, not all Boolean expressions use the equality operator, and the last when expression uses an operator less than ("<"). The case expression in Listing 4 is logically the same as the case expression in Listing 2. Therefore, when I run the code in Listing 4, it will produce the same result as shown in result 1.

If multiple when expressions evaluate to true, what expressions are returned?
In a single case expression, a different when expression evaluates to true may occur. When this happens, SQL Server returns the result expression associated with the first when expression that evaluates to True. Therefore, if the value of multiple when clauses is true, then the order of the When clauses controls the result returned from the case expression.

To demonstrate this, let's use the case expression to display "$ 200 order" when the Orderamt is within the range of $200, when Orderamt displays "100 USD for $100" when Orderamt is less than $100 when "< $100 Order "when Orderamt does not belong to these categories, the order is categorized as" 300 USD and above ". Let's review the code in Listing 5 to show what happens when multiple when expressions evaluate to true when an attempt is made to classify an order to one of the orderamt_category values.

SELECT Orderamt,
Case
When the order amount <300 then ' 200 USD order '
When Orderamt <200 so ' 100 dollar order '
When Orderamt <100 then ' <100 dollar Order '
ELSE ' 300 USD and above orders '
End as Orderamt_category
From MyOrder;
Listing 5: Example of multiple when expressions evaluates to True

When I run the code in Listing 5, V gets the output from result 3.

Orderamt orderamt_category
--------------------------------------- ----------- ---------------
10.59 US $200 Order
200.45 200-dollar order
8.65 US $200 Order
75.38 US $200 Order
123.54 200-dollar order
99.99 200-dollar order
350.17 300 USD and above orders
180.76 US $200 Order
1.49 200-dollar order
Result 3: Run the results of listing 5 o'clock

By looking at the results in results 3, you can see that each order is reported as 200 or more than 300 orders, and we know that this is not true. This happens because I only use the less than ("<") operator to simplify the orders that the collation causes in my case expression to cause multiple when expressions to evaluate to true. The sort of when clause does not allow the return of the correct expression.

by reordering my when clauses, I can get the results I want. The code in Listing 6 is the same as in Listing 5, but I re-ordered the When clause to correctly categorize my order.

SELECT Orderamt,
Case
When Orderamt <100 then ' <100 dollar Order '
When Orderamt <200 so ' 100 dollar order '
When the order amount <300 then ' 200 USD order '
ELSE ' 300 USD and above orders '
End as Orderamt_category
From MyOrder;
Listing 6: Code similar to listing 5, but when clauses are in different order

When I run the code in Listing 5, I get the output from result 4.

Orderamt orderamt_category
--------------------------------------- ----------- ---------------
10.59 <100 US Dollar Order
200.45 200-dollar order
8.65 <100 US Dollar Order
75.38 <100 US Dollar Order
123.54 US $100 order
99.99 <100 US Dollar orders
350.17 300 USD and above orders
180.76 US $100 order
1.49 <100 US Dollar orders
Result 4: Run the results of listing 6 o'clock

By looking at the output in result 4, you can see that by changing the order of the When expressions, I get the correct results for each order.

Nested case expressions
Sometimes you may need to perform additional tests to further categorize the data using case expressions. When this occurs, you can use a nested case expression. The code in Listing 7 shows an example of a nested case expression that further classifies the orders in the Myorder table to determine whether the layaway value is used to purchase the order when the order exceeds $200.

SELECT Orderamt,
Case
When Orderamt <100 then ' <100 dollar Order '
When Orderamt <200 so ' 100 dollar order '
When Orderamt <300 so
Case
When layaway = ' N '
Then ' no layaway $200 orders '
ELSE ' $200 order with Layway ' end
Other
Case
When layaway = ' N '
Then ' no layaway $300 orders '
ELSE ' $300 order with Layway ' end
End as Orderamt_category
From MyOrder;
Listing 7: Nested case statements

The code in Listing 7 is similar to the code in Listing 6. The only difference is that I added an extra case expression to see if the order in the Myorder table was purchased using the layaway option, which is only allowed when the purchase is more than $200. Note that when you nest a case expression, SQL Server only allows up to 10 nesting levels.

Other places where you can use a case expression
So far, all of my examples have used a case expression to place a scenario expression in the select list of a TSQL SELECT statement to create a result string. You can also use case expressions in Update,delete and SET statements. In addition, the case expression can be used in conjunction with the In,where,order by and having clauses. In Listing 8, I used a case that expresses a WHERE clause.

Select
From Myorder
WHERE case Year (Orderdt)
When 2014 so ' first year '
When 2013 then ' the second year '
When 2012 so ' 3 years '
ELSE "Year Four and Beyond" END = "First year";
Listing 8: Using the case expression in the WHERE clause

In Listing 8, I only want to return orders for lines in "Year 1" from the "MyOrder" table. To achieve this, I placed the same case expression in the WHERE clause as I used in Listing 2. I use the case expression as the left part of the Where condition, so it produces a different "year ..." string based on the Orderdt column. I then tested the string generated from the case expression to see if it was equal to the value of "Year 1" when it was returned from the Myorder table. Keep in mind that if there are other better ways, such as using the year function to select a row for a given vintage, I don't recommend using a case expression to select a date from the date column with a sting like "Year 1." I'm just doing this here. Demonstrates how to use a case statement in a WHERE clause.

Quickly switch case expressions using the IIf function
With the introduction of SQL Server 2012, Microsoft added the IIF feature. The IIF feature can be seen as a shortcut to case declarations. In Figure 2, you can find the syntax for the IIF function.

IIF (Boolean expression, True_value,false_value)
Figure 2:iif Syntax of the function

A Boolean expression is a valid Boolean expression that is equivalent to TRUE or false. The "true_value" expression is executed when the Boolean expression is equal to the true value. If the Boolean expression equals False, "False_value" is executed. Like a case expression, the IIf function can be nested to 10 levels.

Examples of using IIF
To demonstrate how to replace a case expression with the IIf function, let's review the code in Listing 9 that uses the search's case expression.

SELECT Orderamt,
Case
When Orderamt> ' High $ Order '
ELSE ' low $ Order ' END as OrderType
From MyOrder;
Listing 9: Example of a simple case expression

The code in Listing 9 has only one when expression that determines whether the Orderamt is high or low. If the When expression "orderamt> 200" evaluates to True, the OrderType value is set to "high $ order". If the when expression evaluates to False, set "low $ order" for the OrderType value.

The rewrite code that uses the IIf function instead of the case expression can be found in Listing 10.

SELECT Orderamt,
IIF (orderamt> 200,
' High $ Order ',
' Low $ Order ') as OrderType
From MyOrder;
Listing 10: An example of using the IIf function

By looking at listing 10, you can see why the IIf function is considered a shorthand version of the case expression. Replace the word "IIF" with a comma, "ELSE" clause with a comma, "END" replaced by "". When the Boolean expression "Orderamt> 200" is true, the value "high $ Order" is displayed. When the Boolean expression "orderamt> 200" is evaluated as false, "low $ Order" is displayed. If you run the code in listings 9 and 10, you'll see that they all produce exactly the same output.

Example of a nested IIF feature
Just like the case expression SQL Server allows you to nest the IIf function. In Listing 11 is an example of a nested IIf function.

SELECT Orderamt,
IIF (Orderamt <100,
' <100 dollar order ',
(IIF (Orderamt <200,
' $100 order ',
(IIF (Orderamt <300,
(IIF (layaway = ' N ',
"No orders for $200 for Layway",
"200-dollar order with layaway"
）
）
(IIF (layaway = ' N ',
"No orders for $300 for layaway",
' With layaway $300 order '
）
）
）
）
）
）
) as Orderamt_category
From MyOrder;
Listing 11: Example of a nested IIF function

In this example, you can see that I have used the IIf function several times. Each additional one is used for the IIf function "True value" or "False value". The code in Listing 11 is the same as the code in Listing 7 that uses a nested case expression.

Limit
As with most tsql functions, this is limited. The following are some limitations on the case and IIF structure.

Case Expression Restrictions:

There can be up to 10 levels of nesting in a case expression.
A case expression cannot be used to control the execution flow of a TSQL statement.
IIF Feature Limitations:

The IIF clause can only be nested at most 10 levels.
Profile
The case expression and the IIf function allow you to place expression logic in the TSQL code, which changes the result of the code based on the evaluation of the expression. By using a comparison expression supported by the IIf function and the case expression, you can execute a different block of code based on whether the comparison expression evaluates to True or FALSE. The case expression and the IIf function provide you with programmatic control to meet business requirements that you may not have.

Questions and Answers
In this section, you can see how the case and IIF constructs are understood by answering the following questions.

Question 1:
There are two different syntactic variants of the case expression: simple and searched. Which of the following two statements best describes the difference between a simple search case expression (select two).

Simple case syntax supports only equality operators, while search case syntax supports multiple operators
The simple case syntax supports multiple operators, whereas the searled case syntax supports only equality operators
The simple case syntax specifies its Boolean expression after the When clause, and the search case syntax has the left side of the Boolean expression after the case statement, and the right side of the Boolean expression after the When clause has the right side of the Boolean expression.
The simple case syntax is on the left side of the Boolean expression after the case statement, to the right of the Boolean expression after the When clause, and the search case expression has a Boolean expression after the When clause
Question 2:
If a case expression has more than one then/else clause, the WHEN clause is evaluated to TRUE,

Executes the then expression of the When clause that evaluates to TRUE.
Executes the then expression of the first when clause with a value of true.
Executes the When clause of all then expressions with a value of true.
else expression is executed
Question 3:
How many nesting levels does the case expression or IIf function have?

8
10
16
32
Reply:
Question 1:
The answer is a and d. A simple case statement can use only the equality operator, whereas the searled case expression handles multiple operators and complex Boolean expressions. In addition, the simple case syntax has the left part of the equality operator after the word case, and the right part of the equality operator after when. searled CASE expression must complete a Boolean operation after the When clause (left part, operator, right part)

Question 2:
The correct answer is B. If multiple when clauses evaluate to TRUE, SQL Server executes only the then portion of the first when clause with a value of true. Any other when clause of all other then clauses is evaluated as true is skipped.

Question 3:
The correct answer is B. The case expression and the IIf function only support up to 10 nesting levels.

This article is the foundation of T-SQL: A Ladder Beyond the foundation

Basics of T-sql: Beyond basic Level 6: Using case expressions and IIF functions