取在不同情况下

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

你可以做围绕这些方针的东西。下面像CASE

Select a,b,c 
from z 
where (@para1 = 0 AND A = A) -- A=A condition is pointless though 
    OR (@para1 = 1 AND a between 1 and 10) 
    OR (@para1 = 2 AND a between 11 and 20) 

Answer 2

的ANDOR作品组合使用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 

Answer 3

试试这个mehtod：

DECLARE @Cond AS NVARCHAR(1000) 
if @para1 = 0 
    SET @Cond = ' a = a' 
else if @para1 = 1 
    SET @Cond = ' a between 1 and 10' 
else if @para1 = 2 
    SET @Cond = ' a between 11 and 20' 

EXEC('Select a,b,c from z where ' + @Cond) 

Answer 4

你可以试试这个

if(@para1=0) 
Begin 
---- 
End 
if(@para1=1) 
Begin 
---- 
End 

或者如果你有多个条件，那么

select * from <tableName> where 1=1 AND (@para1=1 || ----) 
AND (@para1=2 || ----) 

Answer 5

嗨，你可以用参数测试，然后像这样的和运营商开始。

@para1 as 
Select a,b,c 
from z 
where (@para1 = 0) or (@para1 = 1 and a >= 1 and a <= 10) or (@para1 = 2 and a >= 11 and a <= 20); 

注意到a = a将始终返回true，除非将a设置为null。

祝你好运。