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

`IF-ELSE` in a `SELECT` is not allowed, you probably want `CASE WHEN...ELSE`. http://stackoverflow.com/questions/63447/how-do-you-perform-an-if-then-in-an-sql-select — Tim Schmelter, Mar 28 '13 at 14:09
@TimSchmelter I think `IF` is allowed in MySQL which makes this more confusing — JNK, Mar 28 '13 at 14:09
[msdn resource](http://msdn.microsoft.com/en-us/library/ms182587.aspx) — BenFradet, Mar 28 '13 at 14:10

score 20 · Accepted Answer · edited Mar 28 '13 at 14:18

20

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
...

edited Mar 28 '13 at 14:18

ypercubeᵀᴹ

109,746
18
170
231

answered Mar 28 '13 at 14:09

JNK

60,688
15
118
136

1

is `BO.REFERENCELOWERLIMIT <= BO.VALUE <= BO.REFERENCEUPPERLIMIT` valid t-sql? – T I Mar 28 '13 at 14:17
Thank you for all your help – MCP_infiltrator Mar 28 '13 at 14:29

score 5 · Answer 2 · answered Mar 28 '13 at 14:11

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

IF, ELIF, ELSE in T-SQL

2 Answers2