SQL IF clause within WHERE clause

Question

Is it possible to use an IF clause within a WHERE clause in MS SQL   Example   WHERE     IF IsNumeric  OrderNumber    1         OrderNumber    OrderNumber     ELSE         OrderNumber LIKE        OrderNumber

User · Answer

You want the CASE statement  WHERE OrderNumber LIKE CASE WHEN IsNumeric  OrderNumber  1 THEN  OrderNumber ELSE        OrderNumber END

User · Answer

Use a CASE statement instead of IF

User · Answer

The following example executes a query as part of the Boolean expression and then executes slightly different statement blocks based on the result of the Boolean expression  Each statement block starts with BEGIN and completes with END   USE AdventureWorks2012  GO DECLARE  AvgWeight decimal 8 2    BikeCount int IF   SELECT COUNT    FROM Production Product WHERE Name LIKE  Touring-3000      gt  5 BEGIN    SET  BikeCount             SELECT COUNT              FROM Production Product           WHERE Name LIKE  Touring-3000        SET  AvgWeight             SELECT AVG Weight            FROM Production Product           WHERE Name LIKE  Touring-3000        PRINT  There are     CAST  BikeCount AS varchar 3       Touring-3000 bikes      PRINT  The average weight of the top 5 Touring-3000 bikes is     CAST  AvgWeight AS varchar 8          END ELSE  BEGIN SET  AvgWeight             SELECT AVG Weight           FROM Production Product           WHERE Name LIKE  Touring-3000         PRINT  Average weight of the Touring-3000 bikes is     CAST  AvgWeight AS varchar 8           END   GO   Using nested IF   ELSE statements The following example shows how an IF     ELSE statement can be nested inside another  Set the  Number variable to 5  50  and 500 to test each statement   DECLARE  Number int SET  Number   50 IF  Number  gt  100    PRINT  The number is large   ELSE     BEGIN       IF  Number  lt  10       PRINT  The number is small     ELSE       PRINT  The number is medium     END   GO

User · Answer

There isn t a good way to do this in SQL  Some approaches I have seen   1  Use CASE combined with boolean operators   WHERE     OrderNumber   CASE          WHEN  IsNumeric  OrderNumber    1          THEN CONVERT INT   OrderNumber          ELSE -9999 -- Some numeric value that just cannot exist in the column     END     OR      FirstName LIKE CASE         WHEN  IsNumeric  OrderNumber    0          THEN        OrderNumber         ELSE        END   2  Use IF s outside the SELECT  IF  IsNumeric  OrderNumber     1 BEGIN     SELECT   FROM Table     WHERE  OrderNumber   OrderNumber END ELSE BEGIN     SELECT   FROM Table     WHERE OrderNumber LIKE        OrderNumber END   3  Using a long string  compose your SQL statement conditionally  and then use EXEC  The 3rd approach is hideous  but it s almost the only think that works if you have a number of variable conditions like that

User · Answer

I think that where   like     case   then    can work with Booleans  I am using T-SQL   Scenario  Let s say you want to get Person-30 s hobbies if bool is false  and Person-42 s hobbies if bool is true   According to some  hobby-lookups comprise over 90  of business computation cycles  so pay close attn     CREATE PROCEDURE sp Case  bool   bit AS SELECT Person Hobbies FROM Person WHERE Person ID        case  bool          when 0              then 30         when 1             then 42     end

User · Answer

You don t need a IF statement at all   WHERE      IsNumeric  OrderNumber    1 AND OrderNumber    OrderNumber  OR  IsNumeric  OrderNumber    0 AND OrderNumber LIKE        OrderNumber

User · Answer

You want the CASE statement  WHERE OrderNumber LIKE CASE WHEN IsNumeric  OrderNumber  1 THEN  OrderNumber ELSE        OrderNumber END

User · Answer

To clarify some of the logical equivalence solutions  An if statement if  a  then b  is logically equivalent to   a    b   It s the first line on the Logical equivalences involving conditional statements section of the Logical equivalence wikipedia article  To include the else  all you would do is add another conditional if a  then b   if  a  then c   which is logically equivalent to   a    b   amp  amp   a    c  So using the OP as an example  IF IsNumeric  OrderNumber    1     OrderNumber    OrderNumber ELSE     OrderNumber LIKE        OrderNumber        the logical equivalent would be   IsNumeric  OrderNumber   lt  gt  1 OR OrderNumber    OrderNumber  AND  IsNumeric  OrderNumber    1 OR OrderNumber LIKE        OrderNumber

User · Answer

To clarify some of the logical equivalence solutions  An if statement if  a  then b  is logically equivalent to   a    b   It s the first line on the Logical equivalences involving conditional statements section of the Logical equivalence wikipedia article  To include the else  all you would do is add another conditional if a  then b   if  a  then c   which is logically equivalent to   a    b   amp  amp   a    c  So using the OP as an example  IF IsNumeric  OrderNumber    1     OrderNumber    OrderNumber ELSE     OrderNumber LIKE        OrderNumber        the logical equivalent would be   IsNumeric  OrderNumber   lt  gt  1 OR OrderNumber    OrderNumber  AND  IsNumeric  OrderNumber    1 OR OrderNumber LIKE        OrderNumber

User · Answer

In sql server I had same problem I wanted to use an and statement only if parameter is false and on true I had to show both values true and false so I used it this way   T IsPublic    ShowPublic or   ShowPublic   1

User · Answer

USE AdventureWorks2012  GO IF   SELECT COUNT    FROM Production Product WHERE Name LIKE  Touring-3000      gt  5 PRINT  There are more than 5 Touring-3000 bicycles   ELSE PRINT  There are 5 or less Touring-3000 bicycles     GO

User · Answer

You should be able to do this without any IF or CASE   WHERE      IsNumeric  OrderNumber  AND        CAST OrderNumber AS VARCHAR     CAST  OrderNumber AS VARCHAR   OR     NOT IsNumeric  OrderNumber  AND        OrderNumber LIKE         OrderNumber     Depending on the flavour of SQL you may need to tweak the casts on the order number to an INT or VARCHAR depending on whether implicit casts are supported   This is a very common technique in a WHERE clause  If you want to apply some  IF  logic in the WHERE clause all you need to do is add the extra condition with an boolean AND to the section where it needs to be applied

User · Answer

You should be able to do this without any IF or CASE   WHERE      IsNumeric  OrderNumber  AND        CAST OrderNumber AS VARCHAR     CAST  OrderNumber AS VARCHAR   OR     NOT IsNumeric  OrderNumber  AND        OrderNumber LIKE         OrderNumber     Depending on the flavour of SQL you may need to tweak the casts on the order number to an INT or VARCHAR depending on whether implicit casts are supported   This is a very common technique in a WHERE clause  If you want to apply some  IF  logic in the WHERE clause all you need to do is add the extra condition with an boolean AND to the section where it needs to be applied

User · Answer

In sql server I had same problem I wanted to use an and statement only if parameter is false and on true I had to show both values true and false so I used it this way   T IsPublic    ShowPublic or   ShowPublic   1

User · Answer

WHERE  IsNumeric  OrderNumber   lt  gt  1 OR OrderNumber    OrderNumber                AND  IsNumber  OrderNumber    1 OR OrderNumber LIKE                                                       OrderNumber

User · Answer

Use a CASE statement UPDATE  The previous syntax  as pointed out by a few people  doesn t work   You can use CASE as follows   WHERE OrderNumber LIKE   CASE WHEN IsNumeric  OrderNumber    1 THEN       OrderNumber    ELSE            OrderNumber   END   Or you can use an IF statement like  N  J  Reed points out

User · Answer

WHERE OrderNumber LIKE CASE WHEN IsNumeric  OrderNumber    1 THEN  OrderNumber ELSE         OrderNumber END   In line case Condition will work properly

User · Answer

an example for using a stored procedure to select users filtered by country and site CREATE STORED PROCEDURE GetUsers  CountryId int   null   SiteId int   null AS BEGIN SELECT           FROM users          WHERE                 CountryId    CASE WHEN ISNUMERIC  CountryId    1 THEN  CountryId ELSE CountryId END AND                 SiteId       CASE WHEN ISNUMERIC  SiteId    1 THEN  SiteId ELSE SiteId END   END

User · Answer

The following example executes a query as part of the Boolean expression and then executes slightly different statement blocks based on the result of the Boolean expression  Each statement block starts with BEGIN and completes with END   USE AdventureWorks2012  GO DECLARE  AvgWeight decimal 8 2    BikeCount int IF   SELECT COUNT    FROM Production Product WHERE Name LIKE  Touring-3000      gt  5 BEGIN    SET  BikeCount             SELECT COUNT              FROM Production Product           WHERE Name LIKE  Touring-3000        SET  AvgWeight             SELECT AVG Weight            FROM Production Product           WHERE Name LIKE  Touring-3000        PRINT  There are     CAST  BikeCount AS varchar 3       Touring-3000 bikes      PRINT  The average weight of the top 5 Touring-3000 bikes is     CAST  AvgWeight AS varchar 8          END ELSE  BEGIN SET  AvgWeight             SELECT AVG Weight           FROM Production Product           WHERE Name LIKE  Touring-3000         PRINT  Average weight of the Touring-3000 bikes is     CAST  AvgWeight AS varchar 8           END   GO   Using nested IF   ELSE statements The following example shows how an IF     ELSE statement can be nested inside another  Set the  Number variable to 5  50  and 500 to test each statement   DECLARE  Number int SET  Number   50 IF  Number  gt  100    PRINT  The number is large   ELSE     BEGIN       IF  Number  lt  10       PRINT  The number is small     ELSE       PRINT  The number is medium     END   GO

User · Answer

an example for using a stored procedure to select users filtered by country and site CREATE STORED PROCEDURE GetUsers  CountryId int   null   SiteId int   null AS BEGIN SELECT           FROM users          WHERE                 CountryId    CASE WHEN ISNUMERIC  CountryId    1 THEN  CountryId ELSE CountryId END AND                 SiteId       CASE WHEN ISNUMERIC  SiteId    1 THEN  SiteId ELSE SiteId END   END

User · Answer

You should be able to do this without any IF or CASE   WHERE      IsNumeric  OrderNumber  AND        CAST OrderNumber AS VARCHAR     CAST  OrderNumber AS VARCHAR   OR     NOT IsNumeric  OrderNumber  AND        OrderNumber LIKE         OrderNumber     Depending on the flavour of SQL you may need to tweak the casts on the order number to an INT or VARCHAR depending on whether implicit casts are supported   This is a very common technique in a WHERE clause  If you want to apply some  IF  logic in the WHERE clause all you need to do is add the extra condition with an boolean AND to the section where it needs to be applied

User · Answer

WHERE  IsNumeric  OrderNumber   lt  gt  1 OR OrderNumber    OrderNumber                AND  IsNumber  OrderNumber    1 OR OrderNumber LIKE                                                       OrderNumber

User · Answer

There isn t a good way to do this in SQL  Some approaches I have seen   1  Use CASE combined with boolean operators   WHERE     OrderNumber   CASE          WHEN  IsNumeric  OrderNumber    1          THEN CONVERT INT   OrderNumber          ELSE -9999 -- Some numeric value that just cannot exist in the column     END     OR      FirstName LIKE CASE         WHEN  IsNumeric  OrderNumber    0          THEN        OrderNumber         ELSE        END   2  Use IF s outside the SELECT  IF  IsNumeric  OrderNumber     1 BEGIN     SELECT   FROM Table     WHERE  OrderNumber   OrderNumber END ELSE BEGIN     SELECT   FROM Table     WHERE OrderNumber LIKE        OrderNumber END   3  Using a long string  compose your SQL statement conditionally  and then use EXEC  The 3rd approach is hideous  but it s almost the only think that works if you have a number of variable conditions like that

User · Answer

Use a CASE statement UPDATE  The previous syntax  as pointed out by a few people  doesn t work   You can use CASE as follows   WHERE OrderNumber LIKE   CASE WHEN IsNumeric  OrderNumber    1 THEN       OrderNumber    ELSE            OrderNumber   END   Or you can use an IF statement like  N  J  Reed points out

User · Answer

You want the CASE statement  WHERE OrderNumber LIKE CASE WHEN IsNumeric  OrderNumber  1 THEN  OrderNumber ELSE        OrderNumber END

User · Answer

CASE Statement is better option than IF always      WHERE  vfl CreatedDate  gt   CASE WHEN  FromDate IS NULL THEN vfl CreatedDate ELSE   FromDate END     AND vfl CreatedDate lt  CASE WHEN  ToDate IS NULL THEN vfl CreatedDate ELSE  ToDate END

User · Answer

There isn t a good way to do this in SQL  Some approaches I have seen   1  Use CASE combined with boolean operators   WHERE     OrderNumber   CASE          WHEN  IsNumeric  OrderNumber    1          THEN CONVERT INT   OrderNumber          ELSE -9999 -- Some numeric value that just cannot exist in the column     END     OR      FirstName LIKE CASE         WHEN  IsNumeric  OrderNumber    0          THEN        OrderNumber         ELSE        END   2  Use IF s outside the SELECT  IF  IsNumeric  OrderNumber     1 BEGIN     SELECT   FROM Table     WHERE  OrderNumber   OrderNumber END ELSE BEGIN     SELECT   FROM Table     WHERE OrderNumber LIKE        OrderNumber END   3  Using a long string  compose your SQL statement conditionally  and then use EXEC  The 3rd approach is hideous  but it s almost the only think that works if you have a number of variable conditions like that

User · Answer

Use a CASE statement instead of IF

User · Answer

I think that where   like     case   then    can work with Booleans  I am using T-SQL   Scenario  Let s say you want to get Person-30 s hobbies if bool is false  and Person-42 s hobbies if bool is true   According to some  hobby-lookups comprise over 90  of business computation cycles  so pay close attn     CREATE PROCEDURE sp Case  bool   bit AS SELECT Person Hobbies FROM Person WHERE Person ID        case  bool          when 0              then 30         when 1             then 42     end

User · Answer

You should be able to do this without any IF or CASE   WHERE      IsNumeric  OrderNumber  AND        CAST OrderNumber AS VARCHAR     CAST  OrderNumber AS VARCHAR   OR     NOT IsNumeric  OrderNumber  AND        OrderNumber LIKE         OrderNumber     Depending on the flavour of SQL you may need to tweak the casts on the order number to an INT or VARCHAR depending on whether implicit casts are supported   This is a very common technique in a WHERE clause  If you want to apply some  IF  logic in the WHERE clause all you need to do is add the extra condition with an boolean AND to the section where it needs to be applied

User · Answer

Use a CASE statement UPDATE  The previous syntax  as pointed out by a few people  doesn t work   You can use CASE as follows   WHERE OrderNumber LIKE   CASE WHEN IsNumeric  OrderNumber    1 THEN       OrderNumber    ELSE            OrderNumber   END   Or you can use an IF statement like  N  J  Reed points out

User · Answer

USE AdventureWorks2012  GO IF   SELECT COUNT    FROM Production Product WHERE Name LIKE  Touring-3000      gt  5 PRINT  There are more than 5 Touring-3000 bicycles   ELSE PRINT  There are 5 or less Touring-3000 bicycles     GO

User · Answer

You don t need a IF statement at all   WHERE      IsNumeric  OrderNumber    1 AND OrderNumber    OrderNumber  OR  IsNumeric  OrderNumber    0 AND OrderNumber LIKE        OrderNumber

User · Answer

If  LstTransDt is Null                 begin                     Set  OpenQty 0                 end             else                 begin                    Select    OpenQty IsNull Sum ClosingQty  0                       From  ProductAndDepotWiseMonitoring                      Where   Pcd  PCd And PtpCd  PTpCd And TransDt  LstTransDt                       end    See if this helps

User · Answer

Use a CASE statement instead of IF

User · Answer

Use a CASE statement instead of IF

User · Answer

WHERE OrderNumber LIKE CASE WHEN IsNumeric  OrderNumber    1 THEN  OrderNumber ELSE         OrderNumber END   In line case Condition will work properly

User · Answer

There isn t a good way to do this in SQL  Some approaches I have seen   1  Use CASE combined with boolean operators   WHERE     OrderNumber   CASE          WHEN  IsNumeric  OrderNumber    1          THEN CONVERT INT   OrderNumber          ELSE -9999 -- Some numeric value that just cannot exist in the column     END     OR      FirstName LIKE CASE         WHEN  IsNumeric  OrderNumber    0          THEN        OrderNumber         ELSE        END   2  Use IF s outside the SELECT  IF  IsNumeric  OrderNumber     1 BEGIN     SELECT   FROM Table     WHERE  OrderNumber   OrderNumber END ELSE BEGIN     SELECT   FROM Table     WHERE OrderNumber LIKE        OrderNumber END   3  Using a long string  compose your SQL statement conditionally  and then use EXEC  The 3rd approach is hideous  but it s almost the only think that works if you have a number of variable conditions like that

User · Answer

If  LstTransDt is Null                 begin                     Set  OpenQty 0                 end             else                 begin                    Select    OpenQty IsNull Sum ClosingQty  0                       From  ProductAndDepotWiseMonitoring                      Where   Pcd  PCd And PtpCd  PTpCd And TransDt  LstTransDt                       end    See if this helps

User · Answer

You want the CASE statement  WHERE OrderNumber LIKE CASE WHEN IsNumeric  OrderNumber  1 THEN  OrderNumber ELSE        OrderNumber END

User · Answer

CASE Statement is better option than IF always      WHERE  vfl CreatedDate  gt   CASE WHEN  FromDate IS NULL THEN vfl CreatedDate ELSE   FromDate END     AND vfl CreatedDate lt  CASE WHEN  ToDate IS NULL THEN vfl CreatedDate ELSE  ToDate END

User · Answer

Use a CASE statement UPDATE  The previous syntax  as pointed out by a few people  doesn t work   You can use CASE as follows   WHERE OrderNumber LIKE   CASE WHEN IsNumeric  OrderNumber    1 THEN       OrderNumber    ELSE            OrderNumber   END   Or you can use an IF statement like  N  J  Reed points out

[sql] SQL: IF clause within WHERE clause

Examples related to sql

Examples related to sql-server

Examples related to tsql