---
description: "Overview of Case in real life! Again, in real life, we perform different actions depending upon the outcome of different conditions. To elaborate more, consider below example: If flight tickets are le"
title: "CASE Statement & Nested Case in SQL Server: T-SQL Example"
image: https://www.guru99.com/images/case-statement-nested-case-in-sql-server.png
---

[Skip to content](#main)

**⚡ Smart Summary**

CASE is a conditional expression in SQL Server that returns a value based on the first condition that evaluates to true, offering Simple and Searched forms plus nesting inside IF…ELSE, UPDATE, and ORDER BY.

- 🧭 **Conditional value:** A CASE expression returns a value that depends on which WHEN condition is the first to evaluate as true.
- 1️⃣ **Simple CASE:** Simple CASE compares one expression against a list of values and runs an equality check for each WHEN.
- 🔎 **Searched CASE:** Searched CASE evaluates a separate Boolean expression for each WHEN, supporting ranges and inequality operators.
- ➖ **ELSE is optional:** When no WHEN matches and ELSE is omitted, the CASE expression returns NULL.
- 🪆 **Nesting:** CASE can be nested inside another CASE and inside an IF…ELSE statement for multi-level logic.
- 🔧 **Beyond SELECT:** Besides SELECT, CASE works with UPDATE and ORDER BY clauses to drive conditional updates and sorting.

Read More

![CASE Statement and Nested CASE in SQL Server with T-SQL Examples]()

## Overview of Case in real life!

Again, in real life, we perform different actions depending on the outcome of different conditions. To elaborate further, consider the example below:

- If flight tickets are less than $100, then I will visit Los Angeles.
- If flight tickets are between $100 to $200, then I will visit New York.
- If flight tickets are between $200 to $400, then I will visit Europe.
- Otherwise, I will prefer to visit a nearby tourist spot.

Let us consider categorizing the condition and the action separately from the above example:

| **Conditions – Flight Tickets** | **Actions performed, only if Condition is TRUE** |
| --- | --- |
| Less than $100 | Visit Los Angeles |
| Between $100 to $200 | Visit New York |
| Between $200 to $400 | Visit Europe |
| None of the above condition met | Nearby tourist spot |

In the above example, we can see that the outcome of the different conditions governs a separate action. For example, the visitor will perform the act of visiting New York only in the condition that the flight ticket is between $100 to $200. Similarly, the [MS SQL](https://www.guru99.com/ms-sql-server-tutorial.html) CASE statement also provides the capability to execute different T-SQL statements based on the outcome of different conditions.

## What is CASE Statement in SQL Server?

CASE Statement in [SQL Server](https://www.guru99.com/sql-server-introduction.html) is the extension of the [IF…ELSE](https://www.guru99.com/sql-server-if-else.html) statement. Unlike IF…ELSE, where a maximum of only one condition is allowed, CASE allows the user to apply multiple conditions to perform different sets of actions in MS SQL. It returns a corresponding value associated with the condition defined by the user.

Let us learn how to use CASE in [SQL](https://www.guru99.com/sql.html) and its concept in the following sections. In MS SQL, there are two types of CASE:

- Simple CASE
- Searched CASE

## Simple CASE

### The Syntax for Simple Case

```
CASE <Case_Expression>
     WHEN Value_1 THEN Statement_1
     WHEN Value_2 THEN Statement_2
     .
     .
     WHEN Value_N THEN Statement_N
     [ELSE Statement_Else]   
END AS [ALIAS_NAME]
```

Here:

- The parameter Case\_Expression denotes the expression that will eventually be compared to Value\_1, Value\_2, and so on.
- The parameters Statement\_1, Statement\_2… denote the statements that will be executed if Case\_Expression = Value\_1, Case\_Expression = Value\_2, and so on.
- In a nutshell, the condition is whether Case\_Expression = Value\_N, and the action is the execution of Statement\_N if the above result is TRUE.
- ALIAS\_NAME is optional and is the alias name given to the SQL Server CASE statement result. It is mostly used when we use CASE in the SQL Server SELECT clause.

### Rules for Simple Case

- Simple Case only allows an equality check of Case\_Expression with Value\_1 to Value\_N.
- The Case\_Expression is compared with a value, in order, starting from the first value, i.e., Value\_1. Below is the execution approach:
- If Case\_Expression is equivalent to Value\_1, then further WHEN…THEN statements are skipped, and CASE execution will END immediately.
- If Case\_Expression does not match Value\_1, then Case\_Expression is compared with Value\_2 for equivalency. This process of comparing Case\_Expression with a value continues until Case\_Expression finds a matching equivalent value from the set of Value\_1, Value\_2, and so on.
- If nothing matches, then control goes to the ELSE statement, and Statement\_Else is executed.
- ELSE is optional.
- If ELSE is not present and Case\_Expression matches none of the values, then NULL is displayed.

The diagram below illustrates the execution flow of a Simple Case:

[![Flowchart showing how a Simple CASE evaluates each WHEN value in order]()](https://www.guru99.com/images/1/031519_0457_CASEstateme1.png)

### Examples

Assume that we have a table named ‘Guru99’ with two columns, Tutorial\_ID and Tutorial\_name, and four rows, as displayed below. We will use this ‘Guru99’ table in the examples that follow.

[![Sample Guru99 table with Tutorial\_ID and Tutorial\_name columns and four rows]()](https://www.guru99.com/images/1/031519_0457_CASEstateme2.png)

**Query 1: SIMPLE CASE with the NO ELSE option**

```
SELECT Tutorial_ID, Tutorial_name,
CASE Tutorial_name
	WHEN 'SQL' THEN 'SQL is developed by IBM'
	WHEN 'PL/SQL' THEN 'PL/SQL is developed by Oracle Corporation.'
	WHEN 'MS-SQL' THEN 'MS-SQL is developed by Microsoft Corporation.'
END AS Description
FROM Guru99
```

Result: The diagram below explains the execution flow of a SIMPLE CASE with NO ELSE.

[![Result of a Simple CASE with no ELSE, showing a Description for each Tutorial\_name]()](https://www.guru99.com/images/1/031519_0457_CASEstateme3.png)

**Query 2: SIMPLE CASE with the ELSE option**

```
SELECT Tutorial_ID, Tutorial_name,
CASE Tutorial_name
	WHEN 'SQL' THEN 'SQL is developed by IBM'
	WHEN 'PL/SQL' THEN 'PL/SQL is developed by Oracle Corporation.'
	WHEN 'MS-SQL' THEN 'MS-SQL is developed by Microsoft Corporation.'
	ELSE 'This is NO SQL language.'
END AS Description
FROM Guru99
```

Result: The diagram below explains the execution flow of a SIMPLE CASE with ELSE.

[![Result of a Simple CASE with ELSE, returning the default text for unmatched rows]()](https://www.guru99.com/images/1/031519_0457_CASEstateme4.png)

## Searched CASE

### The Syntax for Searched Case

```
CASE 
     WHEN <Boolean_Expression_1> THEN Statement_1
     WHEN <Boolean_Expression_2> THEN Statement_2
     .
     .
     WHEN <Boolean_Expression_N> THEN Statement_N
     [ELSE Statement_Else]   
END AS [ALIAS_NAME]
```

Here:

- The parameter Boolean\_Expression\_1… denotes the expression that will be evaluated for TRUE or FALSE.
- The parameters Statement\_1, Statement\_2… denote the statements that will execute if the corresponding Boolean\_Expression\_1, Boolean\_Expression\_2 result is TRUE.
- In a nutshell, the condition is Boolean\_Expression\_1… and the action is the execution of Statement\_N if the above Boolean\_Expression\_1 is TRUE.
- ALIAS\_NAME is optional and is the alias name given to the CASE statement result. It is mostly used when we use CASE in the SELECT clause.

### Rules for Searched Case

- Unlike the Simple Case, the Searched Case is not restricted to only an equality check but allows a Boolean expression.
- The Boolean expression is evaluated, in order, starting from the first Boolean expression, i.e., Boolean\_Expression\_1. Below is the execution approach:
- If Boolean\_Expression\_1 is TRUE, then further WHEN…THEN statements are skipped, and CASE execution will END immediately.
- If Boolean\_Expression\_1 is FALSE, then Boolean\_Expression\_2 is evaluated for a TRUE condition. This process of assessing the Boolean expression continues until one of the Boolean expressions returns TRUE.
- If nothing matches, then control goes to the ELSE statement, and Statement\_Else is executed.
- Like the Simple Case, ELSE is optional in the Searched Case as well.
- If ELSE is not present and none of the Boolean expressions returns TRUE, then NULL is displayed.

The diagram below illustrates the execution flow of the Searched Case:

[![Flowchart showing how a Searched CASE evaluates each Boolean expression in order]()](https://www.guru99.com/images/1/031519_0457_CASEstateme5.png)

### Examples

**Query 1: SEARCHED CASE with the NO ELSE option**

```
SELECT Tutorial_ID, Tutorial_name,
CASE 
 	WHEN Tutorial_name = 'SQL' THEN 'SQL is developed by IBM'
	WHEN Tutorial_name = 'PL/SQL' THEN 'PL/SQL is developed by Oracle Corporation.'
	WHEN Tutorial_name = 'MS-SQL' THEN 'MS-SQL is developed by Microsoft Corporation.'
END AS Description
FROM Guru99
```

Result: The diagram below explains the execution flow of the SEARCHED CASE with NO ELSE.

[![Result of a Searched CASE with no ELSE, matching each Tutorial\_name to a Description]()](https://www.guru99.com/images/1/031519_0457_CASEstateme6.png)

**Query 2: SEARCHED CASE with the ELSE option**

```
SELECT Tutorial_ID, Tutorial_name,
CASE 
	WHEN Tutorial_name = 'SQL' THEN 'SQL is developed by IBM'
	WHEN Tutorial_name = 'PL/SQL' THEN 'PL/SQL is developed by Oracle Corporation.'
	WHEN Tutorial_name = 'MS-SQL' THEN 'MS-SQL is developed by Microsoft Corporation.'
	ELSE 'This is NO SQL language.'
END AS Description
FROM Guru99
```

Result: The diagram below explains the execution flow of the SEARCHED CASE with ELSE.

[![Result of a Searched CASE with ELSE, returning the default text for non-SQL languages]()](https://www.guru99.com/images/1/031519_0457_CASEstateme7.png)

### Difference between Execution Approach: SIMPLE and SEARCH CASE

Let us have a look at the SIMPLE CASE example below:

```
SELECT Tutorial_ID, Tutorial_name,
CASE Tutorial_name
	WHEN 'SQL' THEN 'SQL is developed by IBM'
	WHEN 'PL/SQL' THEN 'PL/SQL is developed by Oracle Corporation.'
	WHEN 'MS-SQL' THEN 'MS-SQL is developed by Microsoft Corporation.'
	ELSE 'This is NO SQL language.'
END AS Description
FROM Guru99
```

Here, ‘Tutorial\_name’ is a part of the CASE expression in SQL. Then the ‘Tutorial\_name’ value is compared with each WHEN value, i.e., ‘SQL’… until ‘Tutorial\_name’ matches a WHEN value.

On the contrary, the SEARCH CASE example has no CASE expression:

```
SELECT Tutorial_ID, Tutorial_name,
CASE 
 	WHEN Tutorial_name = 'SQL' THEN 'SQL is developed by IBM'
	WHEN Tutorial_name = 'PL/SQL' THEN 'PL/SQL is developed by Oracle Corporation.'
	WHEN Tutorial_name = 'MS-SQL' THEN 'MS-SQL is developed by Microsoft Corporation.'
END AS Description
FROM Guru99
```

Here, each WHEN statement has its own conditional Boolean expression. Each Boolean expression, i.e., Tutorial\_name = ‘SQL’… is evaluated for TRUE/FALSE until the first Boolean expression that evaluates to TRUE.

## Difference between Simple and Searched Case

| **Simple Case** | **Searched Case** |
| --- | --- |
| The CASE keyword is immediately followed by the CASE\_Expression and comes before the WHEN statement. E.g.:<br>CASE \<Case\_Expression><br>WHEN Value\_1 THEN Statement\_1… | The CASE keyword is followed by the WHEN statement, and there is no expression between CASE and WHEN. E.g.:<br>CASE WHEN \<Boolean\_Expression\_1> THEN Statement\_1… |
| In a Simple Case, a VALUE exists for each WHEN statement. These values (Value\_1, Value\_2…) are compared with a single CASE\_Expression sequentially. The result is evaluated for the TRUE/FALSE condition for each WHEN statement. E.g.:<br>CASE \<Case\_Expression><br>WHEN Value\_1 THEN Statement\_1…<br>WHEN Value\_2 THEN Statement\_2… | In a Searched Case, a Boolean\_Expression exists for each WHEN statement. These Boolean expressions (Boolean\_Expression\_1, Boolean\_Expression\_2…) evaluate the TRUE/FALSE condition for each WHEN statement. E.g.:<br>CASE<br>WHEN \<Boolean\_Expression\_1> THEN Statement\_1…<br>WHEN \<Boolean\_Expression\_2> THEN Statement\_2… |
| A Simple Case supports only an equality check, i.e., whether CASE\_Expression = VALUE\_1, VALUE\_2… E.g.:<br>CASE \<Case\_Expression> WHEN Value\_1 THEN Statement\_1…<br>In the above example, the only operation performed by the system is checking if Case\_Expression = Value\_1. | With Boolean\_Expression\_N, a Searched Case supports any operation that results in a Boolean value. It includes the equal and not-equal-to operators. E.g.:<br>CASE WHEN \<Boolean\_Expression\_1> THEN Statement\_1…<br>In the above example, Boolean\_Expression\_1 can contain both the ‘equal to’ and ‘not equal to’ operators, like A = B, A != B. |

**Don't Miss:**

- [Oracle vs SQL Server – Difference Between Them](https://www.guru99.com/oracle-vs-sql-server.html)
- [SQL Server Data Types with Examples](https://www.guru99.com/sql-server-datatype.html)
- [Substring() in SQL Server: How to Use with Example](https://www.guru99.com/sql-server-substring.html)
- [Top 20 SSRS Interview Questions and Answers (2026)](https://www.guru99.com/ssrs-interview-questions.html)

## Nested CASE: CASE in IF ELSE

We can use CASE inside an IF…ELSE statement. We declare a [variable](https://www.guru99.com/sql-server-variable.html) for the flight ticket and branch on its value. Below is the example MS-SQL code:

```
DECLARE @Flight_Ticket int;
SET @Flight_Ticket = 190;
IF @Flight_Ticket > 400
   PRINT 'Visit Nearby Tourist Location';
ELSE 
BEGIN
    SELECT
	CASE 
	WHEN @Flight_Ticket BETWEEN 0 AND 100 THEN 'Visit Los Angeles'
	WHEN @Flight_Ticket BETWEEN 101 AND 200 THEN 'Visit New York'
	WHEN @Flight_Ticket BETWEEN 201 AND 400 THEN 'Visit Europe'
	END AS Location	
END
```

In the above example, CASE is nested inside the IF…ELSE statement. First, the IF statement executes, and if the CASE condition in SQL Server is False, then the ELSE statement executes. The ELSE part contains a nested CASE statement in SQL. Depending upon the flight ticket value, one of the following results is displayed:

- The system prints ‘Visit Nearby Tourist Location’ if flight tickets are > $400.
- The system prints ‘Visit Los Angeles’ if flight tickets are BETWEEN $0 AND $100.
- The system prints ‘Visit New York’ if flight tickets are BETWEEN $101 AND $200.
- The system prints ‘Visit Europe’ if flight tickets are BETWEEN $201 AND $400.

The result below shows the location returned when the nested CASE runs inside the ELSE branch:

[![Result of a CASE nested inside an IF…ELSE, printing the visit location for the ticket]()](https://www.guru99.com/images/1/031519_0457_CASEstateme8.png)

## Nested CASE: CASE inside CASE

We can use CASE inside another CASE in SQL. Below is the example MS-SQL code:

```
DECLARE @Flight_Ticket int;
SET @Flight_Ticket = 250;
SELECT
CASE 
WHEN @Flight_Ticket >= 400 THEN 'Visit Nearby Tourist Location.'
WHEN @Flight_Ticket < 400 THEN 
    	CASE 
		WHEN @Flight_Ticket BETWEEN 0 AND 100 THEN 'Visit Los Angeles'
		WHEN @Flight_Ticket BETWEEN 101 AND 200 THEN 'Visit New York'
		WHEN @Flight_Ticket BETWEEN 201 AND 400 THEN 'Visit Europe'
		END	
END AS Location
```

In the above example, CASE is nested inside another CASE statement. The system executes the outer CASE first; if Flight\_Ticket < $400, the inner CASE runs. The returned location follows the same ticket ranges as the previous example, so a $250 ticket falls in the $201–$400 range and returns ‘Visit Europe’.

The result below shows the location returned by the inner CASE for a $250 ticket:

[![Result of a CASE nested inside another CASE, returning Visit Europe for a 250 ticket]()](https://www.guru99.com/images/1/031519_0457_CASEstateme9.png)

## CASE with UPDATE

Assume again that we have the ‘Guru99’ table with two columns and four rows, as displayed below:

[![Guru99 table before the CASE UPDATE, showing the original Tutorial\_Name values]()](https://www.guru99.com/images/1/031519_0457_CASEstateme10.png)

We can use CASE with UPDATE. Below is the example MS-SQL code:

```
UPDATE Guru99
SET Tutorial_Name = 
	(
	CASE
	WHEN Tutorial_Name = 'SQL' THEN 'Structured Query language.'
	WHEN Tutorial_Name = 'PL/SQL' THEN 'Oracle PL/SQL'
	WHEN Tutorial_Name = 'MSSQL' THEN 'Microsoft SQL.'
	WHEN Tutorial_Name = 'Hadoop' THEN 'Apache Hadoop.'
	END
	)
```

In the above example, CASE is used in the UPDATE statement. Depending upon the Tutorial\_Name value, the Tutorial\_Name column is updated with the THEN statement value:

- If Tutorial\_Name = ‘SQL’, then update Tutorial\_Name to ‘Structured Query language’.
- If Tutorial\_Name = ‘PL/SQL’, then update Tutorial\_Name to ‘Oracle PL/SQL’.
- If Tutorial\_Name = ‘MSSQL’, then update Tutorial\_Name to ‘Microsoft SQL’.
- If Tutorial\_Name = ‘Hadoop’, then update Tutorial\_Name to ‘Apache Hadoop’.

Running the statement updates the matching rows, as shown below:

[![Execution result of the CASE UPDATE statement on the Guru99 table]()](https://www.guru99.com/images/1/031519_0457_CASEstateme11.png)

Let us query the ‘Guru99’ table to check the updated values:

[![Guru99 table after the CASE UPDATE, showing the new Tutorial\_Name values]()](https://www.guru99.com/images/1/031519_0457_CASEstateme12.png)

## CASE with Order by

We can use CASE with ORDER BY. Below is the example MS-SQL code:

```
Declare @Order Int;
Set @Order = 1
Select * from Guru99 order by 
CASE 
	WHEN @Order = 1 THEN Tutorial_ID
	WHEN @Order = 2 THEN Tutorial_Name
	END
DESC
```

Here, CASE is used with ORDER BY. @Order is set to 1, and as the first WHEN Boolean expression evaluates to TRUE, Tutorial\_ID is selected for the ORDER BY condition. The result is shown below:

[![Result of ORDER BY with a CASE expression sorting the Guru99 table by Tutorial\_ID]()](https://www.guru99.com/images/1/031519_0457_CASEstateme13.png)

## Interesting Facts!

- CASE can be nested in another CASE as well as in an IF…ELSE statement.
- In addition to SELECT, CASE can be used with other SQL clauses like UPDATE and ORDER BY.

## FAQs

⚖️ What is the difference between the CASE expression and the IIF() function?

IIF() is a shorthand for a two-outcome CASE expression: it returns one value when the condition is true and another when false. A full CASE expression is more flexible and can test many WHEN conditions in one statement.

🧮 Can a CASE expression be used in a WHERE clause?

Yes. Because CASE returns a value, it can appear in a SELECT list, a WHERE clause, a HAVING clause, and GROUP BY. In a WHERE clause, CASE lets you apply different filter logic depending on other column values.

📊 Can CASE be combined with aggregate functions like SUM or COUNT?

Yes. Placing a CASE expression inside SUM(), COUNT(), or AVG() performs conditional aggregation, totalling or counting only the rows that match each WHEN condition. This technique is often used to build pivot-style summary reports.

🆚 How is CASE different from COALESCE and ISNULL?

COALESCE and ISNULL only return the first non-NULL value from a list. A CASE expression is broader: it evaluates arbitrary Boolean conditions or value matches and returns the result of the first branch that is true.

🅰️ What data type does a CASE expression return?

A CASE expression returns a single data type. SQL Server determines it from the data-type precedence of every THEN and ELSE result, so all branches should return compatible types to avoid conversion errors.

🧩 Can a CASE expression be used inside a view or stored procedure?

Yes. A CASE expression can appear in the SELECT statement of a view and anywhere inside a stored procedure, function, or trigger. The same expression syntax works across all of these objects.

🤖 Can GitHub Copilot generate CASE statements?

Yes. [GitHub Copilot](https://github.com/features/copilot) can draft Simple and Searched CASE expressions, including nested CASE and CASE inside UPDATE, from a natural-language prompt. Always review the WHEN conditions, branch order, and ELSE handling before running the query.

🧠 How does AI help write CASE logic in SQL?

AI and machine-learning assistants translate plain-English rules into Simple or Searched CASE expressions, suggest missing WHEN branches, and flag overlapping or unreachable conditions. The developer reviews each suggestion for correctness before deploying it.

#### Summarize this post with:

ChatGPTPerplexityGrokGoogle AI

**Stay Updated on AI** **Get Weekly AI Skills, Trends, Actionable Advice.**

##### Sign up for the newsletter

Subscribe for Free

You have successfully subscribed.  
Please check your inbox.

![AI-Newsletter]() Chosen by over **350,000+** professionals

[Scroll to top](#wrapper)

×

Search for:

```json
{"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://www.guru99.com/#organization","name":"Guru99","sameAs":["https://www.facebook.com/Guru99Official","https://twitter.com/guru99com"],"logo":{"@type":"ImageObject","@id":"https://www.guru99.com/#logo","url":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","contentUrl":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","caption":"Guru99","inLanguage":"en-US"}},{"@type":"WebSite","@id":"https://www.guru99.com/#website","url":"https://www.guru99.com","name":"Guru99","publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US"},{"@type":"ImageObject","@id":"https://www.guru99.com/images/case-statement-nested-case-in-sql-server.png","url":"https://www.guru99.com/images/case-statement-nested-case-in-sql-server.png","width":"700","height":"250","caption":"CASE Statement &amp; Nested Case in SQL Server","inLanguage":"en-US"},{"@type":"BreadcrumbList","@id":"https://www.guru99.com/sql-server-case.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":"1","item":{"@id":"https://www.guru99.com","name":"Home"}},{"@type":"ListItem","position":"2","item":{"@id":"https://www.guru99.com/sql-server","name":"SQL Server"}},{"@type":"ListItem","position":"3","item":{"@id":"https://www.guru99.com/sql-server-case.html","name":"CASE Statement &#038; Nested Case in SQL Server: T-SQL Example"}}]},{"@type":"WebPage","@id":"https://www.guru99.com/sql-server-case.html#webpage","url":"https://www.guru99.com/sql-server-case.html","name":"CASE Statement &#038; Nested Case in SQL Server: T-SQL Example","dateModified":"2026-10-07T19:19:14+05:30","isPartOf":{"@id":"https://www.guru99.com/#website"},"primaryImageOfPage":{"@id":"https://www.guru99.com/images/case-statement-nested-case-in-sql-server.png"},"inLanguage":"en-US","breadcrumb":{"@id":"https://www.guru99.com/sql-server-case.html#breadcrumb"}},{"@type":"Person","@id":"https://www.guru99.com/author/editorial","name":"Editorial Team","url":"https://www.guru99.com/author/editorial","image":{"@type":"ImageObject","@id":"https://www.guru99.com/images/favicon-new-logo.png","url":"https://www.guru99.com/images/favicon-new-logo.png","caption":"Editorial Team","inLanguage":"en-US"},"sameAs":["https://www.guru99.com/editorial-team"],"worksFor":{"@id":"https://www.guru99.com/#organization"}},{"articleSection":"SQL Server","headline":"CASE Statement &#038; Nested Case in SQL Server: T-SQL Example","description":"Overview of Case in real life! Again, in real life, we perform different actions depending upon the outcome of different conditions. To elaborate more, consider below example: If flight tickets are le","keywords":"sqlserver, sql","speakable":{"@type":"SpeakableSpecification","cssSelector":[".entry-title",".summary"]},"@type":"Article","author":{"@id":"https://www.guru99.com/author/editorial","name":"Editorial Team"},"dateModified":"2026-10-07T19:19:14+05:30","image":{"@id":"https://www.guru99.com/images/case-statement-nested-case-in-sql-server.png"},"copyrightYear":"2026","name":"CASE Statement &#038; Nested Case in SQL Server: T-SQL Example","subjectOf":[{"@type":"FAQPage","mainEntity":[{"@type":"Question","name":"What is the difference between the CASE expression and the IIF() function?","acceptedAnswer":{"@type":"Answer","text":"IIF() is a shorthand for a two-outcome CASE expression: it returns one value when the condition is true and another when false. A full CASE expression is more flexible and can test many WHEN conditions in one statement."}},{"@type":"Question","name":"Can a CASE expression be used in a WHERE clause?","acceptedAnswer":{"@type":"Answer","text":"Yes. Because CASE returns a value, it can appear in a SELECT list, a WHERE clause, a HAVING clause, and GROUP BY. In a WHERE clause, CASE lets you apply different filter logic depending on other column values."}},{"@type":"Question","name":"Can CASE be combined with aggregate functions like SUM or COUNT?","acceptedAnswer":{"@type":"Answer","text":"Yes. Placing a CASE expression inside SUM(), COUNT(), or AVG() performs conditional aggregation, totalling or counting only the rows that match each WHEN condition. This technique is often used to build pivot-style summary reports."}},{"@type":"Question","name":"How is CASE different from COALESCE and ISNULL?","acceptedAnswer":{"@type":"Answer","text":"COALESCE and ISNULL only return the first non-NULL value from a list. A CASE expression is broader: it evaluates arbitrary Boolean conditions or value matches and returns the result of the first branch that is true."}},{"@type":"Question","name":"What data type does a CASE expression return?","acceptedAnswer":{"@type":"Answer","text":"A CASE expression returns a single data type. SQL Server determines it from the data-type precedence of every THEN and ELSE result, so all branches should return compatible types to avoid conversion errors."}},{"@type":"Question","name":"Can a CASE expression be used inside a view or stored procedure?","acceptedAnswer":{"@type":"Answer","text":"Yes. A CASE expression can appear in the SELECT statement of a view and anywhere inside a stored procedure, function, or trigger. The same expression syntax works across all of these objects."}},{"@type":"Question","name":"Can GitHub Copilot generate CASE statements?","acceptedAnswer":{"@type":"Answer","text":"Yes. GitHub Copilot can draft Simple and Searched CASE expressions, including nested CASE and CASE inside UPDATE, from a natural-language prompt. Always review the WHEN conditions, branch order, and ELSE handling before running the query."}},{"@type":"Question","name":"How does AI help write CASE logic in SQL?","acceptedAnswer":{"@type":"Answer","text":"AI and machine-learning assistants translate plain-English rules into Simple or Searched CASE expressions, suggest missing WHEN branches, and flag overlapping or unreachable conditions. The developer reviews each suggestion for correctness before deploying it."}}]}],"@id":"https://www.guru99.com/sql-server-case.html#schema-1150138","isPartOf":{"@id":"https://www.guru99.com/sql-server-case.html#webpage"},"publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US","mainEntityOfPage":{"@id":"https://www.guru99.com/sql-server-case.html#webpage"}}]}
```