sql-server-2005 - 将比较运算符传递给 WHERE 子句

Question

我想使用一个包含比较运算符的参数，例如 '='、'>'、'>=' 并使用它在带有“case when then”的 where 子句中，或者像下面这样的 if 语句:

WHERE  
case @Operator
 when '=' then (@Amount is null) or (@Amount = 0 ) or (Amount= @Amount)  
 when '>' then (@Amount is null) or (@Amount = 0 ) or (Amount> @Amount)  
 when '>=' then (@Amount is null) or (@Amount = 0 ) or (Amount>= @Amount)      
END

Answer 1

我相信这会成功:

WHERE 
  @Amount IS NULL
  OR @Amount = 0
  OR Amount =  CASE @Operator WHEN '='  THEN @Amount END
  OR Amount >  CASE @Operator WHEN '>'  THEN @Amount END
  OR Amount >= CASE @Operator WHEN '>=' THEN @Amount END;