Hi1

Is there any reasonable way to change a query inside a stored procedure?
What I mean is that I have a procedure like this:

CREATE PROCEDURE ReadArticle(
ArticleID INTEGER,
ArticleTitle VARCHAR(100),
ArticleAuthor INTEGER
) RETURNS (
ID INTEGER,
Title VARCHAR(100),
Author INTEGER
) AS
...

All parameters are optional (accept NULLs), they get filled according to
user's input. The procedure should return all rows matching all non-NULL
parameters. Right now I only know of these possibilities:

1) Use a sequence of IFs:

IF (ArticleID IS NOT NULL) THEN
IF (ArticleTitle IS NOT NULL) THEN
IF (ArticleAuthor IS NOT NULL) THEN
FOR SELECT id, title, author FROM articles WHERE id=:ArticleID AND
title=:ArticleTitle AND author=:ArticleAuthor INTO :ID, :Title, :Author DO
SUSPEND;
ELSE
FOR SELECT id, title, author FROM articles WHERE id=:ArticleID AND
title=:ArticleTitle INTO :ID, :Title, :Author DO SUSPEND;
ELSE
IF (ArticleAuthor IS NOT NULL) THEN
...

Obviously, the number of IFs tends to grow extremely quickly (a power of 2)
and I am quite prone to make errors. Also, if I wanted any more complex
processing than just the simple SUSPEND, it would become even less
manageable.

2) Build the query in a variable and then EXECUTE STATEMENT it. This
approach works, but it's rather problematic as soon as a user submits a
string an apostrophe in it, not to mention that there is no preparation for
such a query (so it is slow) and that EXECUTE STATEMENT apparently uses
user's privileges rather than procedure's.

3) Write a more complex set of conditions in the query:

FOR SELECT ... WHERE (:ArticleID IS NULL OR id=:ArticleID) AND
(:ArticleTitle IS NULL OR title=:ArticleTitle) OR (:ArticleAuthor IS NULL OR
author=:ArticleAuthor)

For some reason, this is even slower than the execute statement (but at
least doesn't have a problem with user rights).

I imagine there must be a better way to solve this situation but I just
can't find it. Any help would be welcome.

Thanks,

Pepak