SQL Data Filtering approach

I have a stored procedure that receives 3 parameters that are used to dynamically filter the result set

create proc MyProc  
@Parameter1 int,  
@Parameter2 in开发者_如何学Pythont,  
@Paremeter3 int   
as  
select * from My_table  
where  
1 = case when @Parameter1 = 0 then 1 when @Parameter1 = Column1 then 1 else 0 end  
and  
1 = case when @Parameter2 = 0 then 1 when @Parameter2 = Column2 then 1 else 0 end  
and  
1 = case when @Parameter3 = 0 then 1 when @Parameter3 = Column3 then 1 else 0 end  
return 

The values passed for each parameter can be 0 (for all items) or non-zero for items matching on specific column.

I may have upwards of 20 parameters (example shown only has 3). Is there a more elegant approach to allow this to scale when the database gets large?

I am using something similar to your idea:

select *
from TableA 
where 
    (@Param1 is null or Column1 = @Param1)
    AND (@Param2 is null or Column2 = @Param2)
    AND (@Param3 is null or Column3 = @Param3)

It is generally the same, but I used NULLs as neutral value instead of 0. It is more flexible in a sense that it doesn't matter what is the data type of the @Param variables.

I use a slightly different method to some of the ones listed above and I've not noticed any performance hit. Instead of passing in 0 for no filter I would use null and I would force it to be the default value of the parameter.

As we give the parameters default values it makes them optional which lends itself to better readability when your calling the procedure.

create proc myProc
    @Parameter1 int = null,  
    @Parameter2 int = null,  
    @Paremeter3 int = null
AS
select
    *
from 
    TableA
where
    column1 = coalesce(@Parameter1,column1)
    and
    column2 = coalesce(@Parameter2, column2)
    and
    column3 = coalesce(@Parameter3,column3)

That said I may well try out the dynamic sql method next time to see if I notice any performance difference

Unfortunately dynamic SQL is the best solution performance/stability wise. While all the other methods commonly used ( @param is not or Col = @param, COALESCE, CASE... ) work, they are unpredictable and unreliable, execution plan can (and will) vary for each execution and you may find yourself spending lots of hours trying to figure out, why your query performs really bad when it was working fine yesterday.