Case Statement In SQL Server

Introduction

In this article i will explain about How to implement or use Case statement in SQL Server. 

Practice

While working with an Player Record application their is an requirement to return Gender as Male or Female not in "M" or "F" format. Here i am sharing my code. I have save Gender as M or F type in my table, so based on the gender type i return Full gender type name.

Declare @Gender varchar(50);

SET @Gender = (SELECT TOP 1 GenderType FROM PL_PhysicalDesc

        WHERE PlayerID = 101) 

         SET @Gender = case @Gender  when  'M'   Then  'Male' 

                                                     Else 'Female' 

                END   

Print @Gender

I shared my scenario with you but you can use Case statement by this way in any of your condition. 

Order By cause with Case Statement

SELECT * FROM dbo.Student

ORDER BY 

 CASE WHEN Gender='M' THEN FirstName END Desc,

 CASE WHEN Gender='F' THEN LastName END ASC

Having Clause with Case Statement

SELECT  FirstName 

               ,Gender

               , Total=MAX(Fee) 

                 FROM dbo.Student

                GROUP BY Gender,FirstName

                HAVING (MAX(CASE Gender WHEN  'M' 

                               THEN Fee

                                ELSE NULL END) > 180.00

                 OR MAX(CASE Gender WHEN  'F' 

                                THEN Fee

                                 ELSE NULL END) > 170.00)