sql - 根据不同的情况在 where 子句中获取记录

Question

我正在创建一个带有参数的过程，并且基于一个参数，我必须在 where 子句示例中放置一个不同的过滤器:

@para1 
as  

    Select a,b,c from z 
        where @para1 = 0 then a = a 
          and @para1 = 1  then a between 1 to 10 
          and @para1 = 2 then a  between 11 and 20

Answer 1

使用AND/OR逻辑

SELECT a,b,c
FROM   z
WHERE  @para1 = 0 -- to pull all records when @para1 = 0 
        OR ( @para1 = 1 
             AND a BETWEEN 1 AND 10 ) -- To pull records BETWEEN 1 AND 10 when @para1 = 1
        OR ( @para1 = 2 
             AND a BETWEEN 11 AND 20 ) -- To pull records BETWEEN 11 AND 20 when @para1 = 2