T-SQL中的IF，ELIF，ELSE

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:

我在SQL Server 2008中工作，并尝试在我的代码的SELECT部分​​中使用IF，ELIF，ELSE语句。我想做的是以下内容：

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.

问题是我不明白如何在SQL中执行IF，ELIF，ELSE类型的事务。我试图搜索这种类型的示例并遇到python示例...错误的语言所以我在MSDBN网站上进行了搜索，并没有看到这种工作，只是IF / ELSE。

Thank You

谢谢

2 个解决方案

#1

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

你想要一个CASE表达式。 CASE按顺序进行求值，第一个匹配是查询中返回的内容。

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

#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