CASE Expression in SQL Server

Understanding Case Expression in SQL Server with Example

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)

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:

Hide   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 

Hide   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:

Hide   Shrink    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:

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

Figure 2 - How column "Country" sets value when column code value is inserted / updated

Basic Use in a View

There is nothing new to use CASE expression in a view object. As I mentioned before, CASE expression can be used anywhere scalar expressions are allowed, including in WHERE and HAVING clauses of the select statement.

Conclusion 

I hope that you will get an idea about how to use CASE expression. Enjoy!

SQL Server: Use a CASE Expression in an Update Statement

The CASE expression is used to compare one expression with a set of expressions in SQL. The result of the CASE expression is a Boolean value, true or false. We can use various DML statements like INSERT, SELECT, DELETE and UPDATE with a CASE statement. In this Tech-Recipes tutorial, we will see how to use a CASE expression with UPDATE statements.

We can begin by walking through an example.

Start by creating a table and naming it “employee.”

if exists ( SELECT name from sys.tables where name ='employee')
drop table employee
GO
CREATE TABLE employee
(
    empid INT,
    ename VARCHAR(20),
    sal   INT
);

Insert some sample data in the employee table.

insert into employee values (100, 'jon smith', 50000);
insert into employee values (101, 'mike', 2000);
insert into employee values (102, 'ab luther', 70000);
insert into employee values (103, 'vish dalvi', 60000);

select * from employee;

empid       ename                sal
----------- -------------------- -----------
100         jon smith            50000
101         mike                 2000
102         ab luther            70000
103         vish dalvi           60000

We have inserted four rows and all rows are NON NULL. Thus, all rows have a valid value.

Now let us create another table with empid and sal columns as listed in employee table.

if exists ( SELECT name from sys.tables where name ='emp')
drop table emp
GO
CREATE TABLE emp
  (
     empid INT,
     sal   INT
  );

Now, insert some sample data.

insert into emp values (100, 50000);
insert into emp values (101, NULL);
insert into emp values (102, NULL);
insert into emp values (103, NULL);

select * from emp;

empid       sal
----------- -----------
100         50000
101         NULL
102         NULL
103         NULL

Please note that we have inserted the same empid from the employee table in the emp table, but thesal column is NULL for three employees.

Now, we want to update table emp and set the sal column value equal to the sal column in theemployee table. In the following query, we need to use a CASE expression with the update statement.

UPDATE emp
SET    sal = ( CASE
                 WHEN e2.sal IS NULL THEN e1.sal
                 ELSE e2.sal
               END )
FROM   employee e1 INNER JOIN emp e2
ON     e1.empid = e2.empid;

In the query above, we are checking whether or not the e2.sal column in the emp table is NULL. If it is NULL, then update the value with the sal column of the employee table or else keep it as it is in else condition.

Now, after updating, if you query the emp table, you can see the value for the sal column in the emptable. This has NULL values updated with employee table sal column on the basis of matching empidfrom both the tables using inner join.

select * from emp;

empid       sal
----------- -----------
100         50000
101         2000
102         70000
103         60000