Hi all,

Suppose I have a table t1(f1 int, f2 int, f3 varchar(20)).

I want to create store proc to retrieve rows based on dynamic criteria.
Sometime, I want to retrieve rows that has f1 = 2 and f2 = 5, or want to retrieve rows that only has f3 = 'abc', etc.

I can do that with 2 ways :
1) Create store proc that retrieve all values than issue
sql statement to filter the results with sql like this :
select * from myproc
where field1 = some value
and field2 = some value

2) In my store proc, rows that meets criteria already
filtered, so my store proc will be something
like this :
create store procedure my_proc(criteria1 int,
criteria2 int, ...)

Option no 2 has a better performance, but I have to write every possible criteria.

Is there a better way to create store proc that has good performance without have to write every possible criteria's combination?


Thanks in advance and regards,
Anto.