fetch record in where clause by different cases

Question

I am creating a procedure with parameter and based on a parameter i have to place a different filter in where clause example is:

@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

You could do something around these lines. Below combination of AND OR works like 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

Use AND/OR logic

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

Try this 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

You can try this

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

OR if you have multiple condition then

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

Answer 5

Hi you can begin with the parameter test then an and operator like this.

@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);

notice that a = a will always return true, unless a is set to null.

good luck.