How do I perform an IF...THEN in an SQL SELECT?

How do I perform an `IF...THEN` in an `SQL SELECT` statement?

For example:

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

Use CASE. Something like this.

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

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

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

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

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

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

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

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' )

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