> Let's say the user can search by id, name, customfield1, customfield2.
>
> He can choose not to give any parameters (in this case all the rows are
> shown) or to give some of the parameters. Each parameter that is not
> null is considered in the query.
>
> Now I could of course create if-s in my stored proc, but as in practise
> I have more than 4 conditions, there would be too many variations.
>
> Is there a function or way to put a conditional test in the WHERE part
> of the SELECT?
>
> Like SELECT * FROM MyTable WHERE [if condition1 is not null, then name
> = condition1] AND [if condition2 is not null, then id = condition2] and
> so on...
>
> Is this possible?
>

Hi Zd,
You can use execute statement to build you query inside stored procedure, but that can get ugly with multiple ifs.
If performance is not absolutely critical you can put where outside the procedure like this:

select id, foo, bar from proc
where id = 1

Basically, selectable stored procedure returns result set that is no different that any table and you can treat the result as such. Of course, the for loop inside procedure will execute for all rows, therefore it will be slower than if you put where clause inside it.

Hope this helped.

Sasha