Case Statement in SQL Server

In this tutorial, we are going to learn Case Statement in SQL Server. Case Statement in SQL Server is very similar to Case Statement in

SQL Server has the full support of CASE Statement like any programming language such as C#, Java. You can
check multiple conditions using the CASE statement in SQL Server.

The syntax for defining CASE Statement.

CASE expression

WHEN value_1 THEN result_1
WHEN value_2 THEN result_2
...
WHEN value_n THEN result_n

ELSE result
END

Or you can directly check conditions using the following syntax.

CASE

WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
WHEN condition_n THEN result_n

ELSE result

END

expression:

The expression that will be compared to each of the values provided. (ie: value_1, value_2, … value_n).

value_1, value_2, … value_n:

The values that will be used in the evaluation. Values are evaluated in the order listed. Once a value matches expression, the CASE statement will execute the corresponding statements and not evaluate any further.

condition_1, condition_2, … condition_n:

The conditions that will be evaluated. Conditions are evaluated in the order listed. Once a condition is found to be true, the CASE statement will return the result and not evaluate the conditions any further. All conditions must be the same datatype.

result_1, result_2, … result_n:

The value returned once a condition is found to be true. All values must be the same datatype.

CREATE TABLE Student
(
ID BIGINT,
StudentName VARCHAR(100),
Gender VARCHAR(20),
Marks FLOAT
)

INSERT INTO [dbo].[Student]([ID],[StudentName],[Gender] ,[Marks]) VALUES (1 ,'Amit Pandey' ,'M' ,90)
INSERT INTO [dbo].[Student]([ID],[StudentName],[Gender] ,[Marks]) VALUES (2 ,'Dheeraj Kumar' ,'M' ,30)
INSERT INTO [dbo].[Student]([ID],[StudentName],[Gender] ,[Marks]) VALUES (3 ,'Megha Verma' ,'F' ,76)
INSERT INTO [dbo].[Student]([ID],[StudentName],[Gender] ,[Marks]) VALUES (4 ,'Neha Sharma' ,'F' ,29)

Example using the Case statement:

Example 1:

As you can see i have stored Gender “M” for Male and “F” for Female. If I want to display those values as Male and Female then Case can help me.

SELECT [StudentName],CASE [Gender] WHEN 'M' THEN 'Male' WHEN 'F' THEN 'Female' END AS Gender,[Marks] FROM [dbo].[Student]

Output:

You can write the above query as below.

SELECT [StudentName],CASE  WHEN [Gender]='M' THEN 'Male' WHEN [Gender]='F' THEN 'Female' END AS Gender,[Marks] FROM  [dbo].[Student]

This query will produce the same output.

Example 2:

Suppose I want to display Pass for those students who have marks greater than 33 and Fail for less than 33. We need to write a query as below.

SELECT [StudentName], [Gender], CASE WHEN [Marks]<33 THEN 'Fail' ELSE 'Pass' END FROM [dbo].[Student]

Output:

View More:

• Paging in SQL Server.
• Dynamic Query in SQL Server.
• User Defined Function to Convert Number into Word.
• Difference between @@IDENTITY, IDENT_CURRENT and SCOPE_IDENTITY.

Conclusion:

I hope you have understood the Case statement in SQL Server. I would like to have your Feedback, Comments and suggestions.

Thank You.