sql - SQL：WHERE子句中的IF子句(SQL: IF clause within WHERE clause)

Question

Is it possible to use an IF clause within a WHERE clause in MS SQL?

(是否可以在MS SQL中的WHERE子句中使用IF子句？)

Example:

(例：)

WHERE
    IF IsNumeric(@OrderNumber) = 1
        OrderNumber = @OrderNumber
    ELSE
        OrderNumber LIKE '%' + @OrderNumber + '%'

  ask by Bryan Roth translate from so

Answer 1

Use a CASE statement

(使用CASE语句)
UPDATE: The previous syntax (as pointed out by a few people) doesn't work.

(更新：以前的语法（由少数人指出）不起作用。)

You can use CASE as follows:

(您可以按如下方式使用CASE：)

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

Or you can use an IF statement like @ NJ Reed points out.

(或者您可以使用像@ NJ Reed指出的IF语句。)