SQL Server Case

SQL server "Case" is used when a list of condition are to be checked with a list of value ,base on checking output should be return. There is a provision of else statement in case statement , when no condition match , else condition satisfy, You can define return value when no condition match in the else part.When no else part is there , the query return null. A case statement execute in order from top to bottom.

1)Here is the syntax of Case Statement.

CASE expression

WHEN value1 THEN return1

WHEN value1 THEN return1

...

...

WHEN value_n THEN return_n

ELSE return_n

END

2) Here is the example of Case.

SELECT 

         [Member_Category]= 

      CASE member_id

                         WHEN 1 THEN 'Member 1'

                         WHEN 2 THEN 'Member 3'

                         ELSE 'Other Member'

       END

             FROM tbl_members;

Output :
Member 1
Member 3
Other Member
...
...
...
...
 
You can use  Case in select statement ,you can use nested case statement in your query.You can write sub query in the output statement also.Here is the example.

3)Here is the example of Nested Case Statement.
 

SELECT [Member_Category]=CASE member_id

                    WHEN 1 THEN

                                   CASE gender_id

                                            WHEN 1 THEN 'Male:'+'Member 1'

                                            WHEN 2 THEN 'FeMale:'+'Member 2'

                                            ELSE 'Other Member'

                                      END

                                      WHEN 2 THEN 'Member 2'

                                      ELSE 'Other Member'

            END

FROM tbl_members;

4)You can write sub query in the output statement also.Here is the example.

SELECT 

     [Member_Category]=CASE a.member_id

                                WHEN 1 THEN (SELECT last_name FROM tbl_member WHERE member_id=a.member_id)

                                              ELSE 'Other Member'

                       END

             FROM tbl_member a;

You can you case statement in stored procedure ,functions,triggers ect.You can use case statement in where statement also.Below is the example of case statement use in where.

DECLARE @status_id INT=1

SELECT * FROM tbl_member a

               WHERE (CASE WHEN @status_id > 0 THEN a.member_id ELSE 1 END) =

(CASE WHEN @status_id > 0 THEN @status_id ELSE 1 END)