SQL Server Case Statement

SQL Server  2012 Case Statement

                             Case statement combines multiple IF THEN ELSE statements into single statement for better readability, It can be used search single column or multiple columns

Syntax: Single Column Case Statement

select col1,col2, case  col3

                            when condition1 then msg1

                             when condition2 then msg2   

                             else msg3

                             end    from [tablename]

Example: Using case statement Display message as job title and name

select ename,empno, case  JOB
when 'CLERK' THEN CONCAT(ENAME,' IS A ',JOB)
when 'SALESMAN' THEN CONCAT(ENAME,' IS A ',JOB)
when 'MANAGER' THEN CONCAT(ENAME,' IS A ',JOB)
when 'PRESIDENT' THEN CONCAT(ENAME,' IS A ',JOB)
when 'ANALYST' THEN CONCAT(ENAME,' IS A ',JOB)
end AS "JOB"

 from emp

OUTPUT

EmpName             Emp Number        JOB
-------------------------------------------------------------------------
SMITH                   7369                    SMITH IS A CLERK
ALLEN                   7499                    ALLEN IS A SALESMAN
WARD                    7521                    WARD IS A SALESMAN
JONES                   7566                    JONES IS A MANAGER
MARTIN                  7654                    MARTIN IS A SALESMAN
BLAKE                   7698                    BLAKE IS A MANAGER
CLARK                   7782                    CLARK IS A MANAGER
KING                    7839                    KING IS A PRESIDENT
TURNER                  7844                    TURNER IS A SALESMAN
JAMES                   7900                    JAMES IS A CLERK
FORD                    7902                    FORD IS A ANALYST
MILLER                  7934                    MILLER IS A CLERK

Example:Case statement with Multiple conditions

Syntax:

                         case

                             when boolean exp then msg1;
                             when boolean exp then msg2;

                          end

                              

   Boolean expression in CASE statement/multiple conditions in Case statement

 select ename,empno,SAL, case 
when JOB='CLERK' AND SAL >=800 THEN CONCAT(ENAME,' IS A ',JOB)
when JOB='SALESMAN' AND SAL >=1200 THEN CONCAT(ENAME,' IS A ',JOB)
when JOB='MANAGER'AND SAL >=2000 THEN CONCAT(ENAME,' IS A ',JOB)
when JOB='PRESIDENT'AND SAL >=4000 THEN CONCAT(ENAME,' IS A ',JOB)
when JOB='ANALYST' AND SAL >=2500 THEN CONCAT(ENAME,' IS A ',JOB)
END AS "JOB"
 from emp

OUTPUT:              

ename    empno    SAL    JOB

---------------------------------------------
SMITH    7369    800.00       SMITH IS A CLERK
ALLEN    7499    1600.00     ALLEN IS A SALESMAN
WARD    7521    1250.00      WARD IS A SALESMAN
JONES    7566    2975.00       JONES IS A MANAGER
MARTIN    7654    1250.00    MARTIN IS A SALESMAN
BLAKE    7698    2850.00      BLAKE IS A MANAGER
CLARK    7782    2450.00     CLARK IS A MANAGER
KING    7839    5000.00         KING IS A PRESIDENT
TURNER    7844    1500.00    TURNER IS A SALESMAN
JAMES    7900    950.00          JAMES IS A CLERK
FORD    7902    3000.00          FORD IS A ANALYST
MILLER    7934    1300.00     MILLER IS A CLERK

                               

Tags:SQL Server Case Statement ,T-SQL case statement, using Case statement in select statement,
case statement with single column, case statement with multiple conditions,How to use case statement in T-SQL,How to use case statement in SQL SERVER 2012.