You can do something like (using rdb$database as an example, replace with useful table, parameter and field names):

select * from rdb$database
where rdb$relation_id = coalesce(:param, rdb$relation_id)

If the parameter is NULL, then all rows will be returned. The drawback is that Firebird cannot use an index for this field if used this way. Hence, I find this useful only when used on a small table or combined with selective criteria on fields that do not use this 'trick'.

Unfortunately, excepting EXECUTE STATEMENT, there is no magic way to get Firebird to create a wonderful execution plan, when you refuse to tell anything about what you really want until execution time.

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Zd
Sent: 28. februar 2008 00:10
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Stored procedure to return multiple rows in a single string
Importance: High

Hi Milan,

I think you misunderstood me.

Here is what I'm after:

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?

Thanks:
Zd

----- Original Message -----
From: Milan Babuskov
To: firebird-support@yahoogroups.com
Sent: Wednesday, February 27, 2008 9:42 AM
Subject: Re: [firebird-support] Stored procedure to return multiple rows in a single string


Zd wrote:
> Now just another short question:
> If I were to execute a user defined search in a stored procedure, what would be the best way to achieve this?

Using EXECUTE STATEMENT

--
Milan Babuskov
http://www.flamerobin.org




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



++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links