SQL: IF clause within WHERE clause

sql sql-server tsql

Bryan Roth · Sep 17, 2008 · Viewed 856k times · Source

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

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.