How do you perform an IF...THEN in a SQL SELECT

Question

I want to be able to perform a IF...THEN in an SQL SELECT Statement.

For Example;

SELECT IF(Obsolete = 'N' or InStock = 'Y';1;0) as Salable, * FROM Product

Answer 1

Use the IFF

The IFF() function tests a specified expression and returns one of two strings, based on whether the expression tested was true or false.

Example: Select IFF(curr_bal>0,'Yes','No'), last_name from customer

Answer 2

In T-SQL, use CASE...WHEN in place of IF...THEN

Answer 3

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

Answer 4

Use CASE. Something like this.

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

Answer 5

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

Answer 6

The CASE statement is the closest to IF in SQL.

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

You only need to do the CAST if you want the result as a boolean value.

Answer 7

You use case;

SELECT *,
  Salable =
    CASE 
      WHEN (Obsolete = 'N' AND InStock = 'Y) THEN true
      ELSE false
    END
FROM Product

Answer 8

you can find some nice examples here, and I think the statement that you can use will be something like this:
(from 4guysfromrolla:)

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

Answer 9

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 1 = case when Obsolete = 'N' or InStock = 'Y' then 1 else 0 end

Answer 10

Use a CASE statement:

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

etc...

Answer 11

Case is great and for ORACLE there is a decode function which has its merits. I guess it is like iif in excel

decode('A','A','We have an A','B','We have a B','We have a letter not A or B')

these can contain other decodes. Of course you can use variables as well ;)

Edit :- I just noted from the TAG that you were referring to MS SQL Server so decode is not an option.

Answer 12

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

The extended case:

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

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

Answer 13

From this link: IF THEN ELSE in T-SQL : http://www.databasejournal.com/features/mssql/article.php/3087431/T-SQL-Programming-Part-1---Defining-Variables-and-IFELSE-logic.htm

    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 ?

Answer 14

NVL and NVL2 can be used to test for NULL values.

NVL(a,b) == if 'a' is null then return 'b'.

SELECT nvl(ename, 'No Name') FROM emp; NVL2(a,b,c) == if 'a' is not null then return 'b' else return 'c'.

SELECT nvl2(ename, 'Do have a name', 'No Name') FROM emp;

Answer 15

14 answers, and only 1 guy is smart enough to actually use IF..THE..ELSE, which was the question but his example uses parenthesis so now the user might think that it's a requirement.

Good job!