IF, ELIF, ELSE in T-SQL

Question

I am working in SQL Server 2008 and trying to use a IF, ELIF, ELSE statement in the SELECT section of my code. What I want to do is the following:

IF BO.VALUE < BO.REFERENCELOWERLIMIT
    THEN (BO.VALUE - BO.REFERENCELOWERLIMIT) #I WANT THIS TO BE NEGATIVE
ELSE IF BO.REFERENCELOWERLIMIT <= BO.VALUE <= BO.REFERENCEUPPERLIMIT
    THEN BO.VALUE
ELSE
    (BO.REFERENCEUPPERLIMIT - BO.VALUE)

The problem is that I do not understand how to do a IF, ELIF, ELSE type transaction in SQL. I have tried to search for this type of example and came across python examples...wrong language so I did a search on the MSDBN site and did not see this sort of work, just IF/ELSE.

Thank You

Answer 1

You want a CASE expression. CASE evaluates in order and the first match is what is returned in the query.

SELECT
  CASE WHEN BO.VALUE < BO.REFERENCELOWERLIMIT 
           THEN (BO.VALUE - BO.REFERENCELOWERLIMIT)
       WHEN BO.VALUE BETWEEN BO.REFERENCELOWERLIMIT AND BO.REFERENCEUPPERLIMIT
           THEN BO.VALUE
       ELSE  (BO.REFERENCEUPPERLIMIT - BO.VALUE)
  END as MyColumnAlias
...

Answer 2

SELECT 
    col = CASE 
            WHEN BO.VALUE < BO.REFERENCELOWERLIMIT 
                THEN BO.VALUE - BO.REFERENCELOWERLIMIT
            WHEN BO.VALUE BETWEEN BO.REFERENCELOWERLIMIT AND BO.REFERENCEUPPERLIMIT 
                THEN BO.VALUE
            ELSE BO.REFERENCEUPPERLIMIT - BO.VALUE
FROM tbl