SQL Server Case Expression

SQL SERVER – CASE Statement/Expression Examples and Explanation

CASE expressions can be used in SQL anywhere an expression can be used. Example of where CASE expressions can be used include in the SELECT list, WHERE clauses, HAVING clauses, IN lists, DELETE and UPDATE statements, and inside of built-in functions.

Two basic formulations for CASE expression 
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 equivalency. If these expressions are equivalent, the expression in the THEN clause will be returned.

Syntax:
CASE expressionWHEN expression1 THEN expression1
[[WHEN expression2 THEN expression2] [...]]
[ELSE expressionN]END
Example:
DECLARE @TestVal INT
SET @TestVal = 3SELECTCASE @TestValWHEN 1 THEN 'First'WHEN 2 THEN 'Second'WHEN 3 THEN 'Third'ELSE 'Other'END

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 expressions is shown below:

Syntax:
CASEWHEN Boolean_expression1 THEN expression1
[[WHEN Boolean_expression2 THEN expression2] [...]]
[ELSE expressionN]END
Example:
DECLARE @TestVal INT
SET @TestVal = 5SELECTCASEWHEN @TestVal <=3 THEN 'Top 3'ELSE 'Other'END




----------------------------------




Sometimes, you required to fetch or modify the records based on some conditions. In this case, you may use cursor or loop for modify your records. In this situation Case expression is best alternative for Cursor/looping and also provides better performance.

You can use CASE expressions anywhere in the SQL Query like CASE expressions can be used with in SELECT statement, WHERE clauses, Order by clause, HAVING clauses,Insert, UPDATE and DLETE statements.

Format of CASE expression

The CASE expression has following two formats:

1. Simple CASE expression

   This compares an expression to a set of simple expressions to find the result. This expression compares an expression to the expression in each WHEN clause for equivalency. If the expression with in the WHEN clause is matched, the expression in the THEN clause will be returned.

   Syntax

   
   CASE expression
   WHEN expression1 THEN Result1
   WHEN expression2 THEN Result2
   ELSE ResultN
   END
   
   

2. Searched CASE expressions

   This expression evaluates a set of Boolean expressions to find the result. This expression allows comparison operators, and logical operators AND/OR with in each Boolean expression.

   Syntax

   
   CASE
   WHEN Boolean_expression1 THEN Result1
   WHEN Boolean_expression2 THEN Result2
   ELSE ResultN
   END
   
   

CASE Expression Example

CREATE TABLE dbo.Customer 
( 
CustID INT IDENTITY PRIMARY KEY, 
FirstName VARCHAR(40) NOT NULL, 
LastName VARCHAR(40) NOT NULL, 
StateCode VARCHAR(20) NOT NULL, 
PayRate money NOT NULL DEFAULT 0.00,
Gender VARCHAR(1) NOT NULL, 
) 
GO
 
INSERT INTO dbo.Customer (FirstName, LastName, StateCode, PayRate,Gender) 
VALUES('Tejendra', 'Kumar', 'UP', 150.00,'M')
 
INSERT INTO dbo.Customer (FirstName, LastName, StateCode, PayRate,Gender) 
VALUES('Jolly', 'Kapoor', 'MP', 50.00 ,'F')
 
INSERT INTO dbo.Customer (FirstName, LastName, StateCode, PayRate,Gender) 
VALUES('Pavan', 'Kumar', 'MP', 200.00 ,'M')
 
INSERT INTO dbo.Customer (FirstName, LastName, StateCode, PayRate,Gender) 
VALUES('Boby', 'Sharma', 'DL', 180.00 ,'F')
 
INSERT INTO dbo.Customer (FirstName, LastName, StateCode, PayRate,Gender) 
VALUES('Asif', 'Khan', 'DL', 210.00 ,'M')
GO 
 
SELECT * from Customer

SELECT statement with CASE expressions

--Simple CASE expression: 
SELECT FirstName, State=(CASE StateCode
 WHEN 'MP' THEN 'Madhya Pradesh' 
 WHEN 'UP' THEN 'Uttar Pradesh' 
 WHEN 'DL' THEN 'Delhi' 
 ELSE NULL 
 END), PayRate
FROM dbo.Customer
 
-- Searched CASE expression:
SELECT FirstName,State=(CASE 
 WHEN StateCode = 'MP' THEN 'Madhya Pradesh' 
 WHEN StateCode = 'UP' THEN 'Uttar Pradesh' 
 WHEN StateCode = 'DL' THEN 'Delhi' 
 ELSE NULL 
 END), PayRate
FROM dbo.Customer

Update statement with CASE expression

-- Simple CASE expression: 
UPDATE Customer 
SET StateCode = CASE StateCode
 WHEN 'MP' THEN 'Madhya Pradesh' 
 WHEN 'UP' THEN 'Uttar Pradesh' 
 WHEN 'DL' THEN 'Delhi' 
 ELSE NULL 
 END 
 
-- Simple CASE expression: 
UPDATE Customer 
SET StateCode = CASE 
 WHEN StateCode = 'MP' THEN 'Madhya Pradesh' 
 WHEN StateCode = 'UP' THEN 'Uttar Pradesh' 
 WHEN StateCode = 'DL' THEN 'Delhi' 
 ELSE NULL 
 END 

ORDER BY clause with CASE expressions

-- Simple CASE expression: 
SELECT * FROM dbo.Customer
ORDER BY 
 CASE Gender WHEN 'M' THEN FirstName END Desc,
 CASE Gender WHEN 'F' THEN LastName END ASC
 
-- Searched CASE expression: 
SELECT * FROM dbo.Customer
ORDER BY 
 CASE WHEN Gender='M' THEN FirstName END Desc,
 CASE WHEN Gender='F' THEN LastName END ASC

Having Clause with CASE expression

-- Simple CASE expression: 
SELECT FirstName ,StateCode,Gender, Total=MAX(PayRate)
FROM dbo.Customer
GROUP BY StateCode,Gender,FirstName
HAVING (MAX(CASE Gender WHEN 'M' 
 THEN PayRate 
 ELSE NULL END) > 180.00
 OR MAX(CASE Gender WHEN 'F' 
 THEN PayRate 
 ELSE NULL END) > 170.00)
 
-- Searched CASE expression: 
SELECT FirstName ,StateCode,Gender, Total=MAX(PayRate)
FROM dbo.Customer
GROUP BY StateCode,Gender,FirstName
HAVING (MAX(CASE WHEN Gender = 'M' 
 THEN PayRate 
 ELSE NULL END) > 180.00
 OR MAX(CASE WHEN Gender = 'F' 
 THEN PayRate 
 ELSE NULL END) > 170.00)

What do you think?

I hope you will enjoy the tips while playing with SQL Server. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.

Table of Contents

• Introduction
• What is CASE Expression  
  • Syntax of CASE Expression
  • Sample Example of SQL CASE Statement
• Use of CASE Expression
  • Basic Use in a Stored Procedure
  • Basic Use in a Table Column Formula
  • Basic Use in a View 
• Conclusion
• History

Introduction

This article will give you an idea about how to use CASE expression in T-SQL or as a formula of a particular column.

What is CASE Expression 

CASE is the special scalar expression in SQL language. CASE expression is widely used to facilitate determining / setting a new value from user input values. CASE expression can be used for various purposes which depends on the business logic.

CASE expression is mostly used in SQL stored procedure or as a formula for a particular column, which optimizes the SQL statements.

Syntax of CASE Expression 

SQL CASE expression is used as a type of IF-THEN-ELSE statement. It is similar to switch statement in recent programming languages such as C# and Java. The syntax of the CASE statement is simple as follows:

 Collapse | Copy Code

1.    CASE column_name  
2.      WHEN condition1 THEN result1  
3.      WHEN condition2 THEN result2  
4.      ...  
5.      ELSE result  
6.    END

Sample Example of CASE Statement 

 Collapse | Copy Code

DECLARE @intInput INT
SET @intInput = 2
SELECT CASE(@intInput) WHEN 1 THEN 'One' WHEN 2 THEN 'Two' _
  WHEN 3 THEN 'Three' ELSE 'Your message.' END 

Use of CASE Expression

The case expression can be used anywhere scalar expressions are allowed, including in WHERE and HAVING clauses of the select statement.

In this article, I would like to show the most commonly used case expression in:

• Stored procedure 
• Formula of a particular column 
• View

Basic Use in a Stored Procedure

A simple example of using CASE in a stored procedure is given below:

 Collapse | Copy Code

-- =============================================
-- Author: Md. Marufuzzaman
-- Create date: 
-- Description:    A simple example of CASE expression.
-- =============================================
/*
DECLARE @varCountry VARCHAR(100)
EXEC spGetCountry 1, @varCountry OUTPUT
SELECT @varCountry
*/

ALTER PROCEDURE [dbo].[spGetCountry]
 @intCode        INT
,@varOutPut         VARCHAR(100) OUTPUT
AS
BEGIN
 
SELECT CASE(@intCode) WHEN 1 THEN 'Country_1'
              WHEN 2 THEN 'Country_2'
              WHEN 3 THEN 'Country_3'
              WHEN 4 THEN 'Country_4'
              WHEN 5 THEN 'Country_5'
              WHEN 6 THEN 'Country_6'
              WHEN 7 THEN 'Country_7'
              WHEN 8 THEN 'Country_8'
              WHEN 9 THEN 'Country_9'
              WHEN 10 THEN 'Country_10'         
                      ELSE 'Unknown' END  
      
END

Basic Use in a Table Column Formula

When we create a Table in design mode, SQL server provides us the properties of each column, where we can set various property values like a default value of a column, identity of a column, etc. Every column has a special property that is a custom formula, where you can set your own formula for data manipulation. Let’s take an example:

Our target is to write a formula for a column, and this formula is responsible for setting a new value for another column.

Figure 1 - How we can set a formula for a particular column

A simple example of using CASE in a Table column formula is given below:

 Collapse | Copy Code

// SQL CASE statement
(case [Code] when (1) then 'Country_1' when (2) then 'Country_2' _
 when (3) then 'Country_3' when (4) then 'Country_4' when (5) _
 then 'Country_5' when (6) then 'Country_6' when (7) then 'Country_7' _
 when (8) then 'Country_8' when (9) then 'Country_9' when (10) _
 then 'Country_10' else 'Unknown' end)

When you insert / update a value at column “code”, the SQL server will fire the formula which is associated with the column “code” and finally set the value of that particular column.

http://www.aspdotnet-suresh.com/2012/07/query-to-use-case-statement-in-sql.html

The CASE keyword is used as a conditional operator that considers a value, examines it, and acts on an option depending on the value. The formula of the CASE statement is:

CASE Expression
 WHEN Value1 THEN Result
 WHEN Value2 THEN Result

 WHEN Value_n THEN Result
END

In the following example, a letter that represents a student is provided. If the letter is m or M, a string is created as Male. If the value is provided as f or F, a string is created as Female:

DECLARE @CharGender Char(1),
 @Gender  Varchar(20);
SET @CharGender = 'F';
SET @Gender = 
 CASE @CharGender
  WHEN N'm' THEN N'Male'
  WHEN N'M' THEN N'Male'
  WHEN N'f' THEN N'Female'
  WHEN N'F' THEN N'Female'
 END;

SELECT N'Student Gender: ' + @Gender;
GO

Here is the result of executing it:

CASE...WHEN...THEN...ELSE

In most cases, you may know the only types of value that would be submitted to a CASE statement. In some other cases, an unpredictable value may be submitted. If you anticipate a value other than those you are aware of, the CASE statement provides a "fit-all' alternative by using the last statement as ELSE. In this case, the formula of the CASE statement would be:

CASE Expression
 WHEN Value1 THEN Result
 WHEN Value2 THEN Result
 WHEN Value_n THEN Result
 
 ELSE Alternative
END

The ELSE statement, as the last, is used when none of the values of the WHEN statements fits. Here is an example:

DECLARE @CharGender Char(1),
 @Gender  Varchar(20);
SET @CharGender = 'g';
SET @Gender = 
 CASE @CharGender
  WHEN N'm' THEN N'Male'
  WHEN N'M' THEN N'Male'
  WHEN N'f' THEN N'Female'
  WHEN N'F' THEN N'Female'
  ELSE 'Unknown'
 END;

SELECT N'Student Gender: ' + @Gender;
GO

This would produce:

If you don't produce an ELSE statement but a value not addressed by any of the WHEN statements is produced, the result would be NULL. Here is an example:

This means that it is a valuable safeguard to always include an ELSE sub-statement in a CASE statement.