Reader Level:
Articles

CASE Statement In Transact-SQL

By Sandeep Singh Shekhawat on January 04, 2013
In this article I am going to explain the case statement in Transact-SQL and how to sort table data using the case statement
  • 0
  • 0
  • 6758

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:

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

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

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

COMMENT USING

Trending up