The SQL Case Statement is similar to the control flow statements (something like IF ELSE). This SQL Server Case statement evaluates the series of conditional expressions provided in CASE WHEN and returns the result set. There are two types of SQL Server Case Statements, and they are:
Simple Case Statement: The simple SQL case statement compares the input_expression to a series of test_expressions, followed by the WHEN keyword. Once it found the match, the Case statement will return the corresponding result_expression, followed by the THEN keyword. If there is no match then, the SQL Server Case statement will return the default_expression.
Searched 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 the SQL Server Case statement will evaluate the conditions inside them
- If the condition is TRUE, then the SQL Case statement will return 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 syntax of the SQL Server Case Statement is
-- 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 of the SQL Server Case are:
- Input_Expression: A valid expression that you want to check. For instance, it may be any column on which you want to perform a CASE operation.
- test_expression: You have to specify an expression. SQL Case statement will compare the value or expression against the Input_Expression, and if it 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 CASE operation.
For this CASE Statement in SQL Server demo, we use the Employee table.
SQL Case Example
Let us write a simple case statement in SQL Server. The below query will check for each row present in the MyEmployees table whether the
- 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
- If all the above conditions fail, the SQL Server 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]
Case Statement and Subquery Example
This example use the Subquery inside a Case Statement in SQL Server. First, the Subquery will execute and finds the Average of the Sales amount. Next, the case statement will check whether the Sales are greater than Average Sales (1970.9055), and if it is TRUE, ‘This Employee is Performing Good’ assigned to Remarks Column. Otherwise, it will assign ‘He is Under Performing’. Same as IF ELSE statement.
-- 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
Searched SQL Case Statement Example
Let me write a searched Case Statement in SQL Server or Case with Multiple values.
-- 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]
SQL Case Statement in UPDATE
How to update the records in [MyEmployes Table] with new values using the SQL Sever 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
-- SQL Server Case Statement example 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];
Let us see the output of the Case statement example
Limitations of Case Statement
- We cannot use the SQL Server case statement to control the execution flow of the Statements, User Defined Functions, and Stored Procedures.
- Up to 10 levels of nesting in Case expression is allowed.
- 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.