IF...THEN in an SQL SELECT statement

IF...THEN in an SQL SELECT statement

 The simple case:

Syntax :

SELECT CASE <variable> WHEN <value>      THEN <returnvalue>
                       WHEN <othervalue> THEN <returnthis>
                                         ELSE <returndefaultcase>
       END
FROM <table>

Example: 

SELECT  Working =
        CASE ActiveEmp
        WHEN 'N' THEN 'NO'
        ELSE 'YES'
    END

The extended case:

Syntax :

SELECT CASE WHEN <test>      THEN <returnvalue>
            WHEN <othertest> THEN <returnthis>
                             ELSE <returndefaultcase>
       END
FROM <table>

Example: 

SELECT  CASE WHEN   ActiveEmp = 'N' THEN 'NO'

             WHEN   ActiveEmp = 'Y' THEN 'YES'

                                    ELSE 'INVALID'

        END

FROM    EMPLOYEE_MASTER 

SQL Server 2012 you can use the IIF Function for this.

Syntax :

SELECT IIF(<test>, <returnthis>, <returndefaultcase>) AS <variable>, * 
FROM <table>

Example:   

SELECT IIF(Reorder = 'N' or InStock = 'Y', 1, 0) AS OrderOut, * FROM StockMaster 

In a where clause, use:

Syntax :

where 1 = case when Obsolete = 'N' or InStock = 'Y' then 1 else 0 end