Case Statement in Sql Server
CASE expression in Sql Server
SQL Server CASE expression evaluates a list of conditions and returns a value when the first condition is met (like an if-then-else statement).
Once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.
It will give NULL value when no ELSE block is found and no conditions are met true.
Simple CASE statement:
Searched CASE Statement
evaluates a set of Boolean expressions to find the result
Nested CASE Statement
CASE Statement with ORDER BY Clause
We can also use a CASE statement with an ORDER BY clause, which is used to sort the results in ascending or descending order.
Case Statement in SQL with Group by clause
UPDATE Statement with a CASE Statement
SQL Server also allows us to use the CASE statement with the UPDATE statement. Suppose we want to update student's ages based on CASE statement conditions.
Insert statement with CASE statement
Difference Between Simple and Searched Case
| Simple CASE | Searched CASE |
|---|---|
| There is an expression between the CASE keyword and WHEN clause. For example, CASE | WHEN Condition1 THEN Statement1 There is no expression between the CASE keyword and WHEN clause. For example, CASE WHEN Condition1 THEN Statement1 |
| This statement is used for a simple equality check and determines the result by comparing an expression to a set of multiple values. | This statement tests the conditions for each of the "when" statements separately. It helps us to solve more complex conditions than a simple CASE. |
| The simple CASE statement only supports equality tests. | The searched CASE statement supports any operation that returns a Boolean value with Boolean_Expression. It includes the equal and not equal to operators. |
Limitations of CASE Statement
The CASE statement does not allow us to control the execution flow of stored procedures and functions in SQL Server.
The CASE statement can have several conditions in a Case statement, but it operates only in a sequential model. When one of the conditions becomes true, it stops testing further statements.
The CASE statement does not allow us to NULL values in a table.
