CASE Statement In Transact-SQL

Introduction

In this article, I will explain the case statement in Transact-SQL and how to sort table data using the case statement.

CASE Statement In Transact-SQL

The Transact-SQL CASE...WHEN statement is 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 the SELECT statement, WHERE clauses, HAVING clauses, ORDER BY clause, IN lists, DELETE, and UPDATE statements. SQL Server allows for only ten levels of nesting in CASE expressions.

There are two types of CASE...WHEN expressions, they are:

1. Simple CASE...WHEN expression
2. Searched CASE...WHEN expression

Let's see each type of CASE...WHEN expressions are explained in detail.

Simple CASE...WHEN expression

The CASE function evaluates a variable as well as a column value. 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 simple CASE function has the restriction that it checks only the equality of values. The simple CASE function has the restriction that it checks only the equality of values. When all criteria evaluate 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.

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 two, so the result will be BLACK.

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 operators between each Boolean expression.

But sometimes, more than one Boolean expression can be true; in that case, the first true Boolean expression result value is returned. If there are no true Boolean expressions, then the else will be executed, but the Else statement is optional in a searched CASE...WHEN expression.

Synta

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 that the result will be WHITE.

Example

Sort table rows by a specified column in ascending and descending order.

Step 1. Create a Book Table.

CREATE TABLE BOOK

(
     Id int Identity(1,1) primary key,
     Name nvarchar(50),
     Author nvarchar(50),
     Price decimal(18,2)
)

Step 2. Insert data into the table.

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)

Step 3. All the data for Books looks like this.

Step 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.

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

Step 5. Sort by Author name in ascending order.

declare @column nvarchar(50)= 'Author'
declare @order int = 0
exec SortBooks @column,@order

Conclusion

This article taught us how to explain the case statement in Transact-SQL and how to sort table data using the case statement with a code example program.