SQL CASE Statement

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

Simple Statement: The SQL Server simple case expression compares the input_expression to a series of test_expressions, followed by the WHEN keyword. Once it found the match, the it will return the corresponding result_expression, followed by the THEN keyword. If there is no match then, it will return the default_expression.

Searched SQL Case Statement: This is very useful to perform more powerful and complex comparisons. It has a series of Boolean expressions followed by the WHEN keyword, and it will evaluate the conditions inside them

  • If the condition is TRUE, then it will return result_expression, followed by the THEN keyword.
  • If there is no match, it will return the default_expression provided in the ELSE clause block

SQL Case Statement Syntax

CASE Input_Expression
     WHEN test_expression THEN result_expression
     .........
     ELSE default_expression
END

-- Searched Syntax
WHEN Boolean_expression THEN result_expression
     .........
     ELSE default_expression
END

For the SQL Server Searched case expression syntax, Input_Expression is optional so we can remove it. And the arguments are:

  • Input_Expression: A valid expression that you want to check. For instance, it may be any column on which you want to perform the operation.
  • When Clause: To test multiple expressions.
  • test_expression: You have to specify an expression. SQL Server will compare the case value or expression against the Input_Expression, and if it is TRUE result_expression will return.
  • result_expression: Please provide an expression. If the test_expression is equal to Input_Expression, then this expression value will return. And if they are not equal, then the default_expression.
  • Boolean_expression: Provide a valid Boolean expression that you want to use in searched operation.
  • The optional else statement is helpful to display the default message.

For this demo, we use the EmployeeTb.

Data Source

SQL Server Case Example

Let us write a simple case statement in SQL Server. The following query will check for each row present in the MyEmployees table whether the statement

  • Occupation = Management, and if it is TRUE then it will assign Administrator as 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 assigns Software Developer as Department Name
  • Else clause – If all the above conditions fail, the Server will assign Fresher as the Department Name
SELECT [EmpID]
      ,[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 [EmployeeTb]
SQL CASE Statement 1

Subquery Example

The following query example uses the Subquery inside a Case Statement in SQL Server. First, the Subquery will execute and finds the Average of the Sales amount. Next, it will check whether the Sales are greater than Average Sales (1970.9055), and if it is TRUE, ‘This Employee is Performing Good’ is assigned to Remarks Column. Otherwise, it will assign ‘He is Under Performing’ (else clause). Same as the IF ELSE condition.

SELECT [EmpID]
      ,[FirstName] + '  ' + [LastName] AS FullName
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,CASE 
        WHEN (SELECT AVG([Sales]) FROM [EmployeeTb]) 
                 <= emp.[Sales] THEN 'This Employee is Performing Good'
	ELSE 'He is Under Performing'
	END AS Remarks
FROM [EmployeeTb] AS emp
SQL CASE Statement Subquery 2

Searched SQL Case Statement Example

Let me write a searched Case Statement in SQL Server or with Multiple values.

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[Position] = 
         CASE 
	   WHEN [Sales] < 100 THEN 'Terminate'
	   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 as Regional Manager'
	 END
  FROM EmployeeTb]
Searched SQL CASE Statement 3

SQL Case Statement in UPDATE

How to update the records in [MyEmployes Table] with new values using the SQL Server case statement?. The OUTPUT clause returns a copy of the data that we inserted, deleted, and updated in our tables. Here, we use the OUTPUT Clause to display the old Yearly Income values and the updated income values in one place

UPDATE [EmployeeTb]
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];
SQL Case Statement in UPDATE Query

Let us see the output of the above example

Employee Table After Updating records

Limitations

  • We cannot use this one to control the execution flow of the query, User Defined Functions, and Stored Procedures.
  • Up to 10 levels of nesting expressions are allowed.
  • You cannot use the simple one to check for NULLs.
  • SQL case statement performs its operation sequentially. It means when the condition is satisfied, then it will stop.