Question: How to Use OR condition in CASE WHEN Statement?
Answer: I personally do not like this kind of questions where user is tested on their understanding of the tricky words rather than SQL Server knowledge. However, we find all the kinds of people in the real world and it is practically impossible to educate everyone. In this blog post we will see a question asked for one of my friends in the interview about How to use OR condition in a CASE WHEN statement.
It is practically not possible to use OR statement in CASE statement as the structure of the CASE statement is very different. Here are two different ways how you can use different conditions in the CASE statement.
Method 1: Simple CASE Expressions
A simple CASE expression checks one expression against multiple values. Within a SELECT statement, a simple CASE expression allows only an equality check; no other comparisons are made. A simple CASE expression operates by comparing the first expression to the expression in each WHEN clause for equivalence. If these expressions are equivalent, the expression in the THEN clause will be returned.
DECLARE @TestVal INT SET @TestVal = 3 SELECT CASE @TestVal WHEN 1 THEN 'First' WHEN 2 THEN 'Second' WHEN 3 THEN 'Third' ELSE 'Other' END
Method 2: Searched CASE expressions
A searched CASE expression allows comparison operators, and the use of AND and/or OR between each Boolean expression. The simple CASE expression checks only for equivalent values and can not contain Boolean expressions. The basic syntax for a searched CASE expression is shown below:
DECLARE @TestVal INT SET @TestVal = 5 SELECT CASE WHEN @TestVal >=3 THEN 'Top 3' ELSE 'Other' END
Here are two related blog posts on this subject:
Reference: Pinal Dave (http://blog.SQLAuthority.com)