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.
treatment for erectile dysfunction north drug store canada drug pharmacy
online pharmacy drugstore treatment for erectile dysfunction cheap rx
pharmacies near me erectile dysfunction treatment pharmacy today
discount rx drug rx us pharmacy
best drugstore foundation for dry skin the peoples pharmacy drug store news ce
I’m not that much of a internet reader to be honest but your blogs really nice, keep it up! I’ll go ahead and bookmark your website to come back down the road. Many thanks
As I website possessor I believe the content material here is rattling great , appreciate it for your hard work. You should keep it up forever! Best of luck.