How to Implement switch case in SQL
In sql server. We implement if else type condition or switch condition using case expression
Case expression evaluates a list of conditions and returns one of multiple possible result expressions.
Simplest example of case expression
SELECT count(*),
CASE region
WHEN 'N' then 'North'
WHEN 'W' then 'West'
WHEN null then 'Null'
END Region -- CASE
FROM employee
GROUP BY region;
In this statement, Instead of showing region in short forms like ‘N’,’W’, we are showing region as west, north and so on.
If you see at the working of Switch case, you can always ask question why to use SWITCH CASE when we already have IF-ELSE statement.
The idea behind this is that it makes the code look cleaner and easy to maintain. Apart from this it can do certain things which is either not possible with IF- ELSE or not very straight forward to achieve using IF-ELSE. Moreover, you can use if else condition in stored procedure but not in simple select statement.
Format of case expression:
The CASE expression has two formats:
1) The simple CASE expression compares an expression to a set of simple expressions to determine the result.
2) The searched CASE expression evaluates a set of Boolean expressions to determine the result.
Both formats support an optional ELSE argument.
CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING
Following are the example of case statement with different clauses:
Case when expression combined with group by
select deptno
, case grouping(job)
when 0 then job
when 1 then 'total'
end job
, count(empno) headcount
from emp
group by ROLLUP(deptno, job)
'Case when’ expression in update statement
UPDATE project SET budget = CASE
WHEN budget > 0 and budget < 100000 THEN budget* 1.2
WHEN budget > = 100000 and budget < 200000 THEN budget* 1.1
ELSE budget* 1.05
END
'Case when’ expression in where condition
SELECT column1, column2
FROM viewWhatever
WHERE
@locationID =
CASE @locationType
WHEN 'location' THEN account_location
WHEN 'area' THEN xxx_location_area
WHEN 'division' THEN xxx_location_division
END
'Case when’ expression in ORDER BY clause
Using CASE in an ORDER BY clause
SELECT BusinessEntityID, SalariedFlag
FROM HumanResources.Employee
ORDER BY CASE SalariedFlag
WHEN 1 THEN BusinessEntityID END DESC
,CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END;
GO
Checking for even/odd rows (mod function)
select case mod(empno,2)
when 0 then 'EVEN '
else 'ODD '
end as empno,sum(sal