How do I perform an IF THEN in an SQL SELECT

Question

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

User · Answer

SELECT IIF Obsolete    N  OR InStock    Y  1 0  AS Saleable    FROM Product

User · Answer

As an alternative solution to the CASE statement  a table-driven approach can be used   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

User · Answer

You can have two choices for this to actually implement    Using IIF  which got introduced from SQL Server 2012   SELECT IIF    Obsolete    N  OR InStock    Y    1  0  AS Saleable    FROM Product  Using Select Case   SELECT CASE     WHEN Obsolete    N  or InStock    Y          THEN 1         ELSE 0     END as Saleable        FROM Product

User · Answer

Use a CASE statement   SELECT CASE        WHEN  Obsolete    N  OR InStock    Y          THEN  Y         ELSE  N  END as Available  etc

User · Answer

You can use Case Statement   Select  Case WHEN  Obsolete    N  or InStock    Y   THEN 1 ELSE 0 END Saleable  Product   from Product

User · Answer

From SQL Server 2012 you can use the IIF function for this   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   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      The CASE expression cannot be used to control the flow of execution of   Transact-SQL statements  statement blocks  user-defined functions  and   stored procedures    If your needs can not be satisfied by these limitations  for example  a need to return differently shaped result sets dependent on some condition  then SQL Server does also have a procedural IF keyword   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

User · Answer

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

User · Answer

The case statement is your friend in this situation  and takes one of two forms   The simple case   SELECT CASE  lt variable gt  WHEN  lt value gt       THEN  lt returnvalue gt                         WHEN  lt othervalue gt  THEN  lt returnthis gt                                           ELSE  lt returndefaultcase gt         END AS  lt newcolumnname gt  FROM  lt table gt    The extended case   SELECT CASE WHEN  lt test gt       THEN  lt returnvalue gt              WHEN  lt othertest gt  THEN  lt returnthis gt                               ELSE  lt returndefaultcase gt         END AS  lt newcolumnname gt  FROM  lt table gt    You can even put case statements in an order by clause for really fancy ordering

User · Answer

For the sake of completeness  I would add that SQL uses three-valued logic  The expression   obsolete    N  OR instock    Y    Could produce three distinct results     obsolete   instock   saleable    ---------- --------- ----------    Y          Y         true         Y          N         false        Y          null      null         N          Y         true         N          N         true         N          null      true         null       Y         true         null       N         null         null       null      null         So for example if a product is obsolete but you dont know if product is instock then you dont know if product is saleable  You can write this three-valued logic as follows   SELECT CASE            WHEN obsolete    N  OR instock    Y  THEN  true             WHEN NOT  obsolete    N  OR instock    Y   THEN  false             ELSE NULL        END AS saleable   Once you figure out how it works  you can convert three results to two results by deciding the behavior of null  E g  this would treat null as not saleable   SELECT CASE            WHEN obsolete    N  OR instock    Y  THEN  true             ELSE  false  -- either false or null        END AS saleable

User · Answer

Use CASE   Something like this   SELECT Salable           CASE Obsolete         WHEN  N  THEN 1         ELSE 0     END

User · Answer

SELECT    CAST      CASE WHEN Obsolete    N       or InStock    Y  THEN ELSE 0 END AS bit     as Saleable     FROM    Product

User · Answer

SELECT    CASE       WHEN  Obsolete    N  OR InStock    Y   THEN  YES                                              ELSE  NO    END  as Salable      FROM Product

User · Answer

Question   SELECT IF Obsolete    N  OR InStock    Y    1   0  AS Saleable    FROM Product   ANSI   Select    case when p Obsolete    N     or p InStock    Y  then 1 else 0 end as Saleable     p    FROM    Product p    Using aliases -- p in this case -- will help prevent issues

User · Answer

The CASE statement is the closest to IF in SQL and is supported on all versions of 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   SQL Server Denali  SQL Server 2012  adds the IIF statement which is also available in access  pointed out by Martin Smith    SELECT IIF Obsolete    N  or InStock    Y   1  0  as Saleable    FROM Product

User · Answer

The case statement is your friend in this situation  and takes one of two forms   The simple case   SELECT CASE  lt variable gt  WHEN  lt value gt       THEN  lt returnvalue gt                         WHEN  lt othervalue gt  THEN  lt returnthis gt                                           ELSE  lt returndefaultcase gt         END AS  lt newcolumnname gt  FROM  lt table gt    The extended case   SELECT CASE WHEN  lt test gt       THEN  lt returnvalue gt              WHEN  lt othertest gt  THEN  lt returnthis gt                               ELSE  lt returndefaultcase gt         END AS  lt newcolumnname gt  FROM  lt table gt    You can even put case statements in an order by clause for really fancy ordering

User · Answer

SELECT if  obsolete    N  OR instock    Y    1  0  AS saleable    FROM product

User · Answer

For the sake of completeness  I would add that SQL uses three-valued logic  The expression   obsolete    N  OR instock    Y    Could produce three distinct results     obsolete   instock   saleable    ---------- --------- ----------    Y          Y         true         Y          N         false        Y          null      null         N          Y         true         N          N         true         N          null      true         null       Y         true         null       N         null         null       null      null         So for example if a product is obsolete but you dont know if product is instock then you dont know if product is saleable  You can write this three-valued logic as follows   SELECT CASE            WHEN obsolete    N  OR instock    Y  THEN  true             WHEN NOT  obsolete    N  OR instock    Y   THEN  false             ELSE NULL        END AS saleable   Once you figure out how it works  you can convert three results to two results by deciding the behavior of null  E g  this would treat null as not saleable   SELECT CASE            WHEN obsolete    N  OR instock    Y  THEN  true             ELSE  false  -- either false or null        END AS saleable

User · Answer

Use CASE   Something like this   SELECT Salable           CASE Obsolete         WHEN  N  THEN 1         ELSE 0     END

User · Answer

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

User · Answer

The case statement is your friend in this situation  and takes one of two forms   The simple case   SELECT CASE  lt variable gt  WHEN  lt value gt       THEN  lt returnvalue gt                         WHEN  lt othervalue gt  THEN  lt returnthis gt                                           ELSE  lt returndefaultcase gt         END AS  lt newcolumnname gt  FROM  lt table gt    The extended case   SELECT CASE WHEN  lt test gt       THEN  lt returnvalue gt              WHEN  lt othertest gt  THEN  lt returnthis gt                               ELSE  lt returndefaultcase gt         END AS  lt newcolumnname gt  FROM  lt table gt    You can even put case statements in an order by clause for really fancy ordering

User · Answer

Simple if-else statement in SQL nbsp Server   DECLARE  val INT  SET  val   15   IF  val  lt  25 PRINT  Hi Ravi Anand   ELSE PRINT  By Ravi Anand     GO   Nested If   else statement in SQL nbsp Server -  DECLARE  val INT  SET  val   15   IF  val  lt  25 PRINT  Hi Ravi Anand    ELSE BEGIN IF  val  lt  50   PRINT  what  s up    ELSE   PRINT  Bye Ravi Anand    END   GO

User · Answer

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

User · Answer

You can use Case Statement   Select  Case WHEN  Obsolete    N  or InStock    Y   THEN 1 ELSE 0 END Saleable  Product   from Product

User · Answer

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   amp   c  CheckIfObsolote        FROM      cte  c   See working demo  if then without case in SQL nbsp Server   For start  you need to work out the value of true and false for selected conditions  Here comes two 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   It s the most WYSIWYG method

User · Answer

SELECT    CAST      CASE WHEN Obsolete    N       or InStock    Y  THEN ELSE 0 END AS bit     as Saleable     FROM    Product

User · Answer

Use CASE   Something like this   SELECT Salable           CASE Obsolete         WHEN  N  THEN 1         ELSE 0     END

User · Answer

The CASE statement is the closest to IF in SQL and is supported on all versions of 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   SQL Server Denali  SQL Server 2012  adds the IIF statement which is also available in access  pointed out by Martin Smith    SELECT IIF Obsolete    N  or InStock    Y   1  0  as Saleable    FROM Product

User · Answer

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      SELECT     FirstName  LastName      Salary  DOB      CASE Gender         WHEN  M  THEN  Male          WHEN  F  THEN  Female      END FROM Employees

User · Answer

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

User · Answer

From SQL Server 2012 you can use the IIF function for this   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   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      The CASE expression cannot be used to control the flow of execution of   Transact-SQL statements  statement blocks  user-defined functions  and   stored procedures    If your needs can not be satisfied by these limitations  for example  a need to return differently shaped result sets dependent on some condition  then SQL Server does also have a procedural IF keyword   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

User · Answer

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

User · Answer

Use CASE   Something like this   SELECT Salable           CASE Obsolete         WHEN  N  THEN 1         ELSE 0     END

User · Answer

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

User · Answer

You can have two choices for this to actually implement    Using IIF  which got introduced from SQL Server 2012   SELECT IIF    Obsolete    N  OR InStock    Y    1  0  AS Saleable    FROM Product  Using Select Case   SELECT CASE     WHEN Obsolete    N  or InStock    Y          THEN 1         ELSE 0     END as Saleable        FROM Product

User · Answer

I like the use of the CASE statements but the question asked for an IF statement in the SQL Select   What I ve used in the past has been   SELECT     if GENDER    M   Male   Female   as Gender  FROM       It s like the excel or sheets IF statements where there is a conditional followed by the true condition and then the false condition   if condition  true  false    Furthermore  you can nest the if statements  but then use should use a CASE  -    Note  this works in MySQLWorkbench but may not work in other platforms

User · Answer

A new feature  IIF  that we can simply use   was added in SQL Server 2012   SELECT IIF    Obsolete    N  OR InStock    Y    1  0  AS Saleable    FROM Product

User · Answer

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

User · Answer

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

User · Answer

Question   SELECT IF Obsolete    N  OR InStock    Y    1   0  AS Saleable    FROM Product   ANSI   Select    case when p Obsolete    N     or p InStock    Y  then 1 else 0 end as Saleable     p    FROM    Product p    Using aliases -- p in this case -- will help prevent issues

User · Answer

The CASE statement is the closest to IF in SQL and is supported on all versions of 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   SQL Server Denali  SQL Server 2012  adds the IIF statement which is also available in access  pointed out by Martin Smith    SELECT IIF Obsolete    N  or InStock    Y   1  0  as Saleable    FROM Product

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

From this link  we can understand IF THEN ELSE in T-SQL   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

User · Answer

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

User · Answer

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

User · Answer

I like the use of the CASE statements but the question asked for an IF statement in the SQL Select   What I ve used in the past has been   SELECT     if GENDER    M   Male   Female   as Gender  FROM       It s like the excel or sheets IF statements where there is a conditional followed by the true condition and then the false condition   if condition  true  false    Furthermore  you can nest the if statements  but then use should use a CASE  -    Note  this works in MySQLWorkbench but may not work in other platforms

User · Answer

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

User · Answer

As an alternative solution to the CASE statement  a table-driven approach can be used   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

User · Answer

It will be something like that   SELECT OrderID  Quantity  CASE     WHEN Quantity  gt  30 THEN  The quantity is greater than 30      WHEN Quantity   30 THEN  The quantity is 30      ELSE  The quantity is under 30  END AS QuantityText FROM OrderDetails

User · Answer

Use a CASE statement   SELECT CASE        WHEN  Obsolete    N  OR InStock    Y          THEN  Y         ELSE  N  END as Available  etc

User · Answer

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

User · Answer

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

User · Answer

It will be something like that   SELECT OrderID  Quantity  CASE     WHEN Quantity  gt  30 THEN  The quantity is greater than 30      WHEN Quantity   30 THEN  The quantity is 30      ELSE  The quantity is under 30  END AS QuantityText FROM OrderDetails

User · Answer

SELECT    CASE       WHEN  Obsolete    N  OR InStock    Y   THEN  YES                                              ELSE  NO    END  as Salable      FROM Product

User · Answer

SELECT if  obsolete    N  OR instock    Y    1  0  AS saleable    FROM product

User · Answer

Using SQL CASE is just like normal If   Else statements  In below query  If obsolete value    N  or If InStock value    Y  Then Output will be 1  Otherwise output will be 0  Then we put that 0 or 1 value under the Salable Column   SELECT       CASE          WHEN obsolete    N  OR InStock    Y           THEN 1          ELSE 0        END AS Salable            FROM PRODUCT

User · Answer

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

User · Answer

SELECT    CASE       WHEN  Obsolete    N  OR InStock    Y   THEN  YES                                              ELSE  NO    END  as Salable      FROM Product

User · Answer

Using SQL CASE is just like normal If   Else statements  In below query  If obsolete value    N  or If InStock value    Y  Then Output will be 1  Otherwise output will be 0  Then we put that 0 or 1 value under the Salable Column   SELECT       CASE          WHEN obsolete    N  OR InStock    Y           THEN 1          ELSE 0        END AS Salable            FROM PRODUCT

User · Answer

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

User · Answer

From this link  we can understand IF THEN ELSE in T-SQL   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

User · Answer

Simple if-else statement in SQL nbsp Server   DECLARE  val INT  SET  val   15   IF  val  lt  25 PRINT  Hi Ravi Anand   ELSE PRINT  By Ravi Anand     GO   Nested If   else statement in SQL nbsp Server -  DECLARE  val INT  SET  val   15   IF  val  lt  25 PRINT  Hi Ravi Anand    ELSE BEGIN IF  val  lt  50   PRINT  what  s up    ELSE   PRINT  Bye Ravi Anand    END   GO

User · Answer

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

User · Answer

A new feature  IIF  that we can simply use   was added in SQL Server 2012   SELECT IIF    Obsolete    N  OR InStock    Y    1  0  AS Saleable    FROM Product

User · Answer

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

User · Answer

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      SELECT     FirstName  LastName      Salary  DOB      CASE Gender         WHEN  M  THEN  Male          WHEN  F  THEN  Female      END FROM Employees

User · Answer

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   amp   c  CheckIfObsolote        FROM      cte  c   See working demo  if then without case in SQL nbsp Server   For start  you need to work out the value of true and false for selected conditions  Here comes two 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   It s the most WYSIWYG method

User · Answer

SELECT    CASE       WHEN  Obsolete    N  OR InStock    Y   THEN  YES                                              ELSE  NO    END  as Salable      FROM Product

User · Answer

Use a CASE statement   SELECT CASE        WHEN  Obsolete    N  OR InStock    Y          THEN  Y         ELSE  N  END as Available  etc

User · Answer

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      SELECT     FirstName  LastName      Salary  DOB      CASE Gender         WHEN  M  THEN  Male          WHEN  F  THEN  Female      END FROM Employees

User · Answer

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

User · Answer

The CASE statement is the closest to IF in SQL and is supported on all versions of 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   SQL Server Denali  SQL Server 2012  adds the IIF statement which is also available in access  pointed out by Martin Smith    SELECT IIF Obsolete    N  or InStock    Y   1  0  as Saleable    FROM Product

User · Answer

SELECT IIF Obsolete    N  OR InStock    Y  1 0  AS Saleable    FROM Product

User · Answer

Use a CASE statement   SELECT CASE        WHEN  Obsolete    N  OR InStock    Y          THEN  Y         ELSE  N  END as Available  etc

User · Answer

The case statement is your friend in this situation  and takes one of two forms   The simple case   SELECT CASE  lt variable gt  WHEN  lt value gt       THEN  lt returnvalue gt                         WHEN  lt othervalue gt  THEN  lt returnthis gt                                           ELSE  lt returndefaultcase gt         END AS  lt newcolumnname gt  FROM  lt table gt    The extended case   SELECT CASE WHEN  lt test gt       THEN  lt returnvalue gt              WHEN  lt othertest gt  THEN  lt returnthis gt                               ELSE  lt returndefaultcase gt         END AS  lt newcolumnname gt  FROM  lt table gt    You can even put case statements in an order by clause for really fancy ordering

User · Answer

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      SELECT     FirstName  LastName      Salary  DOB      CASE Gender         WHEN  M  THEN  Male          WHEN  F  THEN  Female      END FROM Employees

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to if-statement

Examples related to case