Hi Sasha,

I'd be using stored procedures to avoid on-the-fly query processing and speed up execution time.

I guess by using DML statements I'd be at the same place...

WHERE outside the selectable stored proc is not an option due to speed.

My program knows how to prepare the query, but I thought there would be a way to do this from a stored proc, without a bunch of ugly ifs... (I'm trying to use stored procs instead of dynamically prepared querys where possible)

Thanks!

----- Original Message -----
From: Sasha Matijasic
To: firebird-support@yahoogroups.com
Sent: Thursday, February 28, 2008 12:24 AM
Subject: RE: [firebird-support] Stored procedure to return multiple rows in a single string


> 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





[Non-text portions of this message have been removed]