The Transact-SQL CASE...WHEN statement is very similar to a switch or case statement in other computer languages. The CASE...WHEN statement is the scalar expression in Transact-SQL that evaluates a list of conditions and returns a result value.
The CASE...WHEN expressions that can be used include in the SELECT statement, WHERE clauses, HAVING clauses, ORDER BY clause, IN lists, DELETE and UPDATE statements. SQL Server allows for only 10 levels of nesting in CASE expressions.
There are two types of CASE...WHEN expressions; they are:
- Simple CASE...WHEN expression
- Searched CASE...WHEN expression
Let's see each type of CASE...WHEN expressions explained in detail.
- Simple CASE...WHEN expression
The CASE function allows us to evaluate a column value on a row against multiple criteria, where each criterion might return a different value. The first criterion that evaluates to true will be the value returned by the CASE function. The CASE function evaluates a variable as well a column value. When all criteria evaluates false then the CASE function returns, otherwise the else expression is the result value. But the else statement is an optional statement in the CASE...WHEN simple expression. The simple CASE function the has the restriction that it checks only equality of values.
Syntax:
CASE input_expression
WHEN when_clause THEN result_value
// @@@@@@@
ELSE else_result_expression
END
DECLARE @color int = 2
SELECT CASE @color
WHEN 1 THEN 'WHITE'
WHEN 2 THEN 'BLACK'
ELSE 'OTHER'
END AS COLOR
Its output will be BLACK because the @color value is 2 so the result value will be BLACK.
2. Searched CASE...WHEN expression
The searched CASE...WHEN expression evaluates a set of Boolean expressions to determine the result. The first Boolean expression that evaluates to true will be the value returned by the CASE function. Here the CASE function does not have any expression but the WHEN clause has a Boolean expression. It allows all comparison operators and we can also use "AND" and "OR" SQL opertaors between each Boolean expression.
But sometimes it is possible for more than one Boolean expression to be true; in that case the first true Boolean expression result value is returned. If there are not any true Boolean expressions then the else will be executed but the Else statement is optional in a searched CASE...WHEN expression.
Syntax:
CASE
WHEN Boolean_expression THEN result_value
// @@@@@@@@@
ELSE else_result_expression
END
DECLARE @color int = 2
SELECT CASE
WHEN @color>=1 AND @color<=2 THEN 'WHITE'
WHEN @color>=3 THEN 'BLACK'
ELSE 'OTHER'
END AS COLOR
In the above code our first Boolean expression is true for the @color variable so the result will be WHITE.
Example:
Sort table rows by a specified column in ascending and descending order.
1. Create a Book Table, as in:
CREATE TABLE BOOK
(
Id int Identity(1,1) primary key,
Name nvarchar(50),
Author nvarchar(50),
Price decimal(18,2)
)
2. Insert data into the table, as in:
INSERT INTO BOOK (Name,Author,Price)
VALUES ('ASP.NET 3.5 UNLEASHED','Stephen Walther',600),
('DATA STRUCTURES', 'SEYMOUR LIPSCHUTZ',300),
('jQuery UI','Eric Sarrion',200)
3. All the data for Books looks like:
![1.PNG]()
4. Create a Stored Procedure to sort table rows in ascending or descending order. The default is ascending order of the Name of the books. See:
CREATE PROCEDURE SortBooks
(
@column nvarchar(50)='Name',
@order int = 0
)
AS
BEGIN
SET NOCOUNT ON;
SELECT Id,Name,Author,Price FROM BOOK ORDER BY
CASE
WHEN @column='Name' AND @order=0 THEN Name END,
CASE
WHEN @column='Name' AND @order=1 THEN Name END DESC,
CASE
WHEN @column='Author' AND @order=0 THEN Author END,
CASE
WHEN @column='Author' AND @order=1 THEN Author END DESC,
CASE
WHEN @column='Price' AND @order=0 THEN Price END,
CASE
WHEN @column='Price' AND @order=1 THEN Price END DESC
END
5. Sort by Author name in ascending order:
declare @column nvarchar(50)= 'Author'
declare @order int = 0
exec SortBooks @column,@order
![2.PNG]()