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 + '%'

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

You don't need a IF statement at all.

WHERE
    (IsNumeric(@OrderNumber) = 1 AND OrderNumber = @OrderNumber)
OR (IsNumeric(@OrderNumber) = 0 AND OrderNumber LIKE '%' + @OrderNumber + '%')

Answer 5

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;

Answer 6

WHERE (IsNumeric(@OrderNumber) <> 1 OR OrderNumber = @OrderNumber) 
             AND (IsNumber(@OrderNumber) = 1 OR OrderNumber LIKE '%' 
                                              + @OrderNumber + '%')