1434  

The CASE statement is the closest to IF in SQL and is supported on all versions of SQL Server

CASE语句是SQL中最接近IF的语句，并且在SQL Server的所有版本中都受到支持

SELECT CAST(
             CASE 
                  WHEN Obsolete = 'N' or InStock = 'Y' 
                     THEN 1 
                  ELSE 0 
             END AS bit) as Saleable, * 
FROM Product

You only need to do the CAST if you want the result as a boolean value, if you are happy with an int, this works:

如果你想要得到一个布尔值的结果，你只需要做转换，如果你对一个整数感到满意，这个方法是:

SELECT CASE 
            WHEN Obsolete = 'N' or InStock = 'Y' 
               THEN 1 
               ELSE 0 
       END as Saleable, * 
FROM Product

CASE statements can be embedded in other CASE statements and even included in aggregates.

CASE语句可以嵌入到其他CASE语句中，甚至包括在聚合中。

SQL Server Denali (SQL Server 2012) adds the IIF statement which is also available in access: (pointed out by Martin Smith)

SQL Server Denali (SQL Server 2012)添加了IIF语句，该语句也可以访问:(由Martin Smith指出)

SELECT IIF(Obsolete = 'N' or InStock = 'Y', 1, 0) as Saleable, * FROM Product

276  

The case statement is your friend in this situation, and takes one of two forms:

案例陈述是你在这种情况下的朋友，并采取以下两种形式之一:

The simple case:

简单的例子:

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

The extended case:

扩展的例子:

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

You can even put case statements in an order by clause for really fancy ordering.

您甚至可以将case语句放在order by子句中，以便进行真正奇妙的排序。

206  

From SQL Server 2012 you can use the IIF function for this.

从SQL Server 2012，您可以使用IIF函数来实现这一点。

SELECT IIF(Obsolete = 'N' OR InStock = 'Y', 1, 0) AS Salable, *
FROM   Product 

This is effectively just a shorthand (albeit not standard SQL) way of writing CASE.

这实际上只是一种编写案例的简写方式(尽管不是标准的SQL)。

I prefer the conciseness when compared with the expanded CASE version.

与扩展的案例版本相比，我更喜欢简洁。

Both IIF() and CASE resolve as expressions within a SQL Statement and can only be used in well defined places.

IIF()和CASE解析都作为SQL语句中的表达式，只能在定义良好的地方使用。

The CASE expression cannot be used to control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures.

不能使用CASE表达式来控制Transact-SQL语句、语句块、用户定义函数和存储过程的执行流。

If your needs can not be satisfied by these limitations (for example a need to return differently shaped result sets dependant on some condition) then SQL Server does also have a procedural IF Keyword.

如果您的需求不能满足这些限制(例如，需要返回不同的形状结果集依赖于某些条件)，那么SQL Server也有一个过程性的If关键字。

IF @IncludeExtendedInformation = 1 
  BEGIN 
      SELECT A,B,C,X,Y,Z 
      FROM   T 
  END 
ELSE 
  BEGIN 
      SELECT A,B,C 
      FROM   T 
  END 

Care must sometimes be taken to avoid parameter sniffing issues with this approach however.

但是，有时必须注意避免使用这种方法的参数嗅探问题。

70  

You can find some nice examples in The Power of SQL CASE Statements, and I think the statement that you can use will be something like this (from 4guysfromrolla):

您可以在SQL CASE语句的威力中找到一些很好的例子，我认为您可以使用的语句是这样的(来自4guysfromrolla):

SELECT
    FirstName, LastName,
    Salary, DOB,
    CASE Gender
        WHEN 'M' THEN 'Male'
        WHEN 'F' THEN 'Female'
    END
FROM Employees

66  

Use CASE. Something like this.

用例。是这样的。

SELECT Salable =
        CASE Obsolete
        WHEN 'N' THEN 1
        ELSE 0
    END

41  

SELECT  
(CASE 
     WHEN (Obsolete = 'N' OR InStock = 'Y') THEN 'YES'
                                            ELSE 'NO' 
 END) as Salable
, * 
FROM Product

35  

 SELECT
   CASE 
      WHEN OBSOLETE = 'N' or InStock = 'Y' THEN 'TRUE' 
      ELSE 'FALSE' 
   END AS Salable,
   * 
FROM PRODUCT

34  

Microsoft SQL Server (T-SQL)

Microsoft SQL Server(t - SQL)

In a select use:

在选择使用:

select case when Obsolete = 'N' or InStock = 'Y' then 'YES' else 'NO' end

In a where clause, use:

在where子句中，使用:

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

33  

From this link, we can uderstand IF THEN ELSE in T-SQL :

通过这个链接，我们可以在T-SQL中uderstand IF:

IF EXISTS(SELECT *
          FROM   Northwind.dbo.Customers
          WHERE  CustomerId = 'ALFKI')
  PRINT 'Need to update Customer Record ALFKI'
ELSE
  PRINT 'Need to add Customer Record ALFKI'

IF EXISTS(SELECT *
          FROM   Northwind.dbo.Customers
          WHERE  CustomerId = 'LARSE')
  PRINT 'Need to update Customer Record LARSE'
ELSE
  PRINT 'Need to add Customer Record LARSE' 

Isn't this good enough for T-SQL ?

这对T-SQL来说不够好吗?

24  

Simple if-else statement in SQL Server:

SQL Server中简单的if-else语句:

DECLARE @val INT;
SET @val = 15;

IF @val < 25
PRINT 'Hi Ravi Anand';
ELSE
PRINT 'By Ravi Anand.';

GO

Nested If...else statement in sql server -

嵌套的If…sql server -中的else语句

DECLARE @val INT;
SET @val = 15;

IF @val < 25
PRINT 'Hi Ravi Anand.';
ELSE
BEGIN
IF @val < 50
  PRINT 'what''s up?';
ELSE
  PRINT 'Bye Ravi Anand.';
END;

GO

17  

Use pure bit logic:

使用纯逻辑:

DECLARE @Product TABLE (
    id INT PRIMARY KEY IDENTITY NOT NULL
   ,Obsolote CHAR(1)
   ,Instock CHAR(1)
)

INSERT INTO @Product ([Obsolote], [Instock])
    VALUES ('N', 'N'), ('N', 'Y'), ('Y', 'Y'), ('Y', 'N')

;
WITH cte
AS
(
    SELECT
        'CheckIfInstock' = CAST(ISNULL(NULLIF(ISNULL(NULLIF(p.[Instock], 'Y'), 1), 'N'), 0) AS BIT)
       ,'CheckIfObsolote' = CAST(ISNULL(NULLIF(ISNULL(NULLIF(p.[Obsolote], 'N'), 0), 'Y'), 1) AS BIT)
       ,*
    FROM
        @Product AS p
)
SELECT
    'Salable' = c.[CheckIfInstock] & ~c.[CheckIfObsolote]
   ,*
FROM
    [cte] c

See working demo: IF THEN WITHOUT CASE IN MSSQL

参见工作演示:如果在MSSQL中没有实例。

For start, you need to work out the value of true and false for selected conditions. Here comes two NULLIF:

首先，您需要计算出对于所选条件的true和false的值。来了两个NULLIF:

for true: ISNULL(NULLIF(p.[Instock], 'Y'), 1)
for false: ISNULL(NULLIF(p.[Instock], 'N'), 0)

combined together gives 1 or 0. Next use bitwise operators.

合在一起得到1或0。下一个使用位操作符。

It's the most WYSIWYG method.

这是最省油的方法。

17  

A new feature, IIF (that we can simply use), was added in SQL Server 2012:

SQL Server 2012增加了一个新的特性IIF(我们可以简单地使用):

SELECT IIF ( (Obsolete = 'N' OR InStock = 'Y'), 1, 0) AS Saleable, * FROM Product

16  

Use a CASE statement:

使用一个CASE语句:

SELECT CASE
       WHEN (Obsolete = 'N' OR InStock = 'Y')
       THEN 'Y'
       ELSE 'N'
END as Available

etc...

12  

SELECT 1 AS Saleable, *
  FROM @Product
 WHERE ( Obsolete = 'N' OR InStock = 'Y' )
UNION
SELECT 0 AS Saleable, *
  FROM @Product
 WHERE NOT ( Obsolete = 'N' OR InStock = 'Y' )

10  

SELECT CASE WHEN profile.nrefillno = 0 THEN 'N' ELSE 'R'END as newref
From profile

8  

case statement some what similar to if in SQL server

SELECT CASE 
            WHEN Obsolete = 'N' or InStock = 'Y' 
               THEN 1 
               ELSE 0 
       END as Saleable, * 
FROM Product

8  

This isn't an answer, just an example of a CASE statement in use where I work. It has a nested CASE statement. Now you know why my eyes are crossed.

这不是一个答案，只是我工作的地方使用的CASE语句的一个例子。它有一个嵌套的CASE语句。现在你知道为什么我的眼睛在祈祷了。

 CASE orweb2.dbo.Inventory.RegulatingAgencyName
    WHEN 'Region 1'
        THEN orweb2.dbo.CountyStateAgContactInfo.ContactState
    WHEN 'Region 2'
        THEN orweb2.dbo.CountyStateAgContactInfo.ContactState
    WHEN 'Region 3'
        THEN orweb2.dbo.CountyStateAgContactInfo.ContactState
    WHEN 'DEPT OF AGRICULTURE'
        THEN orweb2.dbo.CountyStateAgContactInfo.ContactAg
    ELSE (
            CASE orweb2.dbo.CountyStateAgContactInfo.IsContract
                WHEN 1
                    THEN orweb2.dbo.CountyStateAgContactInfo.ContactCounty
                ELSE orweb2.dbo.CountyStateAgContactInfo.ContactState
                END
            )
    END AS [County Contact Name]

7  

If you're inserting results into a table for the first time, rather than transferring results from one table to another, this works in Oracle 11.2g:

如果您是第一次将结果插入到表中，而不是将结果从一个表转移到另一个表，这在Oracle 11.2g中是有效的:

INSERT INTO customers (last_name, first_name, city)
    SELECT 'Doe', 'John', 'Chicago' FROM dual
    WHERE NOT EXISTS 
        (SELECT '1' from customers 
            where last_name = 'Doe' 
            and first_name = 'John'
            and city = 'Chicago');

4  

  SELECT IIF(Obsolete = 'N' OR InStock = 'Y',1,0) AS Saleable, * FROM Product

3  

For those who uses SQL Server 2012, IIF is a feature that has been added and works as an alternative to Case statements.

对于使用SQL Server 2012的用户来说，IIF是一个已经添加的特性，可以作为Case语句的替代。

SELECT IIF(Obsolete = 'N' OR InStock = 'Y', 1, 0) AS Salable, *
FROM   Product 

2  

As an alternative solution to the CASE statement table driven approach can be used.

作为CASE语句驱动方法的替代解决方案，可以使用表驱动方法。

DECLARE @Product TABLE (ID INT, Obsolete VARCHAR(10), InStock VARCHAR(10)) 
INSERT INTO @Product VALUES
(1,'N','Y'),
(2,'A','B'),
(3,'N','B'),
(4,'A','Y')

SELECT P.* , ISNULL(Stmt.Saleable,0) Saleable
FROM 
    @Product P
    LEFT JOIN 
        ( VALUES
            ( 'N', 'Y', 1 )
        ) Stmt (Obsolete, InStock, Saleable)
        ON  P.InStock = Stmt.InStock OR P.Obsolete = Stmt.Obsolete 

Result:

结果:

ID          Obsolete   InStock    Saleable
----------- ---------- ---------- -----------
1           N          Y          1
2           A          B          0
3           N          B          1
4           A          Y          1

1  

SELECT CASE WHEN Obsolete = 'N' or InStock = 'Y' THEN 1 ELSE 0 
             END AS Saleable, * 
FROM Product