Case Expression in SQL Server

Case Expression is used when we want output-based different conditions. You might think that for that purpose If-Else is there, but here’s a difference. If-Else is a flow control statement, while Case is not. If-Else spans across multiple SQL queries when Case is used in a single SQL query.

This statement assesses a checklist of requirements and yields one of the numerous potential result expressions. This expression has two configurations: The straightforward CASE expression corresponds an expression to a collection of uncomplicated expressions to choose the outcome. The explored CASE expression assesses a collection of Boolean expressions to specify the outcome. This expression can be employed in any declaration or clause that permits a reasonable expression.

It assesses its prerequisites successively and blocks with the first situation whose condition is met. In some circumstances, a declaration is assessed before a CASE expression accepts the outcomes of the declaration as to its intake. Errors in assessing these values are attainable. Aggregate expressions that occur in WHEN statements to a CASE expression are assessed first then delivered to the CASE expression.

The SQL Case is identical to the control flow statements in SQL. This statement assesses the sequence of conditional phrases delivered in WHEN and yields the result set.

The straightforward case expression analogizes the input_expression to a sequence of test_expressions, pursued by the WHEN keyword. Once it encountered the match, it will produce the related result_expression, pursued by the THEN keyword.

The searched SQL Case Statement is extremely beneficial to accomplish more effective and difficult comparisons. It has a string of Boolean expressions obeyed by the WHEN keyword, and it will assess the requirements inside them. The important point to remember here is that only 10 levels of nesting are permitted. It executes its function consecutively. It indicates when the requirement is met then it will discontinue.

Let’s take an example to make things clear. If we want to give messages to students based on grades they received, we can write a case statement as below.

It’ll give the output ‘Well DONE’.

Now, let’s dig in more. There are two types of Case statements.

1. Simple Case Statement
2. Searched Case Statement

Simple Case Statement

In a simple case, one input is compared with different expressions, and based on a match result is picked. Below is the syntax:

If the input is null then the result will be null regardless of the condition. If no expression is matched then the expression in else is picked, and in case else is not specified then null is selected. The example displayed earlier is a simple case expression.

Searched Case Statement

The problem with a simple case statement is that they can only perform an equality check. If we want to use other predicates then we go for a searched case. Below is the syntax:

Example:

In this example, as you can see, we’ve used different types of predicates which are simply not possible in simple case expressions.

Other Points to Know

1. We can have nested cases. For example, check the statement below:

SQL Server only allows nesting up to 10 levels.

2. If we are using an aggregate statement they are evaluated first, and sometimes it can cause a runtime error. So we are advised to use aggregate statements wisely. Look at the below example taken from BOL:

3. We can use case statements in INSERT, UPDATE, DELETE statements too. And also in clauses like WHERE, IN, ORDER BY, HAVING. For more details visit BOL.

   

About the Author

ByteScout Team of Writers ByteScout has a team of professional writers proficient in different technical topics. We select the best writers to cover interesting and trending topics for our readers. We love developers and we hope our articles help you learn about programming and programmers.