SQL: IF clause within WHERE clause

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.

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.

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.

Use a CASE statement instead of IF.

You want the CASE statement

WHERE OrderNumber LIKE
CASE WHEN IsNumeric(@OrderNumber)=1 THEN @OrderNumber ELSE '%' + @OrderNumber END

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) && (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) <> 1 OR OrderNumber = @OrderNumber)
AND (IsNumeric(@OrderNumber) = 1 OR OrderNumber LIKE '%' + @OrderNumber + '%' )

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

// take from the input countryId AND/OR siteId if exists else don't filter

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

CASE Statement is better option than IF always.

  WHERE  vfl.CreatedDate >= CASE WHEN @FromDate IS NULL THEN vfl.CreatedDate ELSE  @FromDate END
    AND vfl.CreatedDate<=CASE WHEN @ToDate IS NULL THEN vfl.CreatedDate ELSE @ToDate END 

    WHERE OrderNumber LIKE CASE WHEN IsNumeric(@OrderNumber) = 1 THEN @OrderNumber ELSE  '%' + @OrderNumber END

In line case Condition will work properly.

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)