Tutorial Gateway

SQL CASE Statement

by

The SQL Case Statement is similar to the control flow statements (something like IF ELSE). This evaluates the series of conditional expressions provided in CASE WHEN, and returns the result set. There are two type of Case Statements in SQL Server, and they are:

  • Simple Case Statement: The simple case statement compares the input_expression to a series of test_expressions followed by the WHEN keyword. Once it found the match, Case statement will return the corresponding result_expression followed by the THEN keyword. If there is no match then, Case statement will return the default_expression.
  • Searched Case Statement: The is very useful to perform more powerful, and complex comparisons. It has series of Boolean expressions followed by the WHEN keyword, and the Case statement will evaluate the conditions inside them
    • If the condition is TRUE then Case statement will return the corresponding result_expression followed by the THEN keyword.
    • If there is no match, Case statement will return the default_expression provided in the ELSE block

SQL Case Statement Syntax

The basic syntax behind the Case Statement in SQL Server is as shown below:

-- Simple SQL Server Case Statement Syntax
CASE Input_Expression
     WHEN test_expression THEN result_expression
     .........
     ELSE default_expression
END

-- Searched SQL Server Case Statement Syntax
CASE 
     WHEN Boolean_expression THEN result_expression
     .........
     ELSE default_expression
END

Arguments are:

  • Input_Expression: Please specify a valid expression that you want to check. For instance, it may be any column on which you want to perform CASE operation.
  • test_expression: You have specify a valid expression. CASE statement will compare the value, or an expression against the Input_Expression, and if it TRUE result_expression will be returned.
  • result_expression: Please specify a valid expression.
    • If the test_expression is equal to Input_Expression then this expression value will be returned as output.
    • If they are not equal then default_expression will be returned as output.
  • Boolean_expression: Please specify a valid Boolean expression that you want to use in searched CASE operation.

In this article we will show you, How to write a CASE Statement in SQL Server with example. For this demonstration, We are going to use the [Employee table] table present in our [SQL Tutorial] Database.

From the below figure you can observe that, [Employee table] table have fourteen records

SQL Server Case Statement 0

MS SQL Case Statement Example

In this example we will show you, How to write a simple case statement in SQL Server. Below SQL query will check for each and every row present in the MyEmployees table whether the

  • Occupation = Management, and if it is TRUE then it will assign Administrator as the Department Name
  • Occupation = Professional, and if it is TRUE, it will assign Sr. Software Developer as the Department Name
  • Occupation = Skilled manual, and if it is TRUE then it assign Software Developer as the Department Name
  • If all the above conditions fails then, it will assign Fresher as the Department Name
-- SQL Server Case Statement example 
SELECT [EmployeeID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,CASE [Occupation]
	   WHEN 'Management' THEN 'Administrators'
	   WHEN 'Professional' THEN 'Sr. Software Developer'
	   WHEN 'Skilled Manual' THEN 'Software Developer'
	   ELSE 'Freshers'
	END AS [Department Name]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [SQL Tutorial].[dbo].[MyEmployees Table]

OUTPUT

SQL CASE Statement 1

SQL Case Statement Example 2

This example will show you, How to use the Subquery inside the Case Statement with example. Here, SQL Server will execute subquery, and the subquery will find the Average of Sales amount. Next, CASE statement will check whether the

  • Sales is greater than Average Sales (1970.9055), and if it is TRUE, ‘This Employee is Performing Good’ will be assigned to Remarks Column.
  • Otherwise, it will assign ‘He is Under Performing’
-- SQL Server Case Statement example 
USE [SQL Tutorial]
GO
SELECT [EmployeeId]
      ,[FirstName] + '  ' + [LastName] AS FullName
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,CASE 
        WHEN (SELECT AVG([Sales]) FROM [Employee Table]) 
                 <= emp.[Sales] THEN 'This Employee is Performing Good'
	ELSE 'He is Under Performing'
	END AS Remarks
FROM [MyEmployees Table] AS emp

OUTPUT

SQL CASE Statement 2

Searched SQL Case Statement Example

In this example we will show you, How to write a searched Case Statement in SQL Server.

-- Searched SQL Server Case Statement example 
SELECT [EmployeeID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[Position] = 
         CASE 
	   WHEN [Sales] < 100 THEN 'Terminate him'
	   WHEN [Sales] >= 100 AND [Sales] <= 600 THEN 'Last Warning'
	   WHEN [Sales] > 600 AND [Sales] < 2000 THEN 'Good Job'
	   WHEN [Sales] >= 2000 AND [Sales] < 4500 THEN 'Great Job - Increase Sales Commission by 1%'
	   ELSE 'Promote him as Regional Manager'
	 END
  FROM [SQL Tutorial].[dbo].[MyEmployees Table]

OUTPUT

SQL CASE Statement 3

SQL Case Statement in UPDATE

In this example we will show you, How to update the records in [MyEmployes Table] with new values using the SQL Sever case statement.

TIP: The OUTPUT clause returns a copy of data that we inserted, deleted, and updated in our tables. Here, we are going to use the OUTPUT Clause to display the old Yearly Income values and the updated income values at one place.

-- SQL Server Case Statement example 
USE [SQL Tutorial]
GO
UPDATE [MyEmployees Table]
SET [YearlyIncome] = (
	CASE 
	   WHEN [Sales] < 100 THEN [YearlyIncome] - 10000
	   WHEN [Sales] >= 100 AND [Sales] <= 600 THEN [YearlyIncome] - 5000
	   WHEN [Sales] > 600 AND [Sales] < 2000 THEN [YearlyIncome] + 5000
	   WHEN [Sales] >= 2000 AND [Sales] < 4500 THEN [YearlyIncome] + 25000
	   ELSE [YearlyIncome] + 55000
        END
	)
OUTPUT inserted.[YearlyIncome] AS [New Income], 
       deleted.[YearlyIncome] AS [Old Income];

OUTPUT

SQL CASE Statement 4

Let us see the output

OUTPUT

SQL CASE Statement 5

Limitations of SQL Server Case Statement

  • We cannot use the SQL Server case statement to control the execution flow of SQL Statements, User Defined functions, and Stored Procedures.
  • Up to 10 levels of nesting in CASE expression is allowed by SQL server.
  • You cannot use the simple CASE statement to check for NULLs.
  • The Case statement performs its operation sequentially, it means when the condition is satisfied then it will stop.

Thank You for Visiting Our Blog

Placed Under: SQL