Pepak,

I've just moved across from MSSQL to Firebird, and have yet to write a Firebird stored procedure - so I'm not familiar yet with Firebird's syntax, but the principle here is the same in both databases I believe.

The problem - return all records matching any non-null user-supplied parameters representing particular columns.

One approach:
a) Define two program variables for each user-supplied parameter, eg P1Low & P1High, P2Low & P2High etc
b) Initialise them to the extremes for that datatype or as required by the application
(eg -999999999 * +999999999 for integers, ' ' & 'zzzzzzzzzz' for strings)
c) If the user supplies a value for that parameter, set both the high and low variables to that value

Then write your (single) SQL statement as follows:
select field1, field2
from tablename
where field1 between P1Low and P1High
and field2 between P2Low and P2High

If any of the fields are nullable, say field2 in this example, modify the SQL statement to:
select field1, field2
from tablename
where field1 between P1Low and P1High
and coalesce(field2,P2Low) between P2Low and P2High

To illustrate, connect to the default Employee database supplied with Firebird and check the principle with the following statements. In the Department table there are nulls in the Mngr_no column, but not in the Location column.

To return all 21 records from the Department table with the basic statement above run:

select location, mngr_no
from department
where location between ' ' and 'zzzzzzzzzzz' and coalesce(mngr_no,-1) between -1 and 9999;

If the user supplies both 'Tokyo' and '118' then the statement becomes:

select location, mngr_no from department where location between 'Tokyo' and 'Tokyo' and coalesce(mngr_no,-1) between 118 and 118;

If the user supplies only '118' then the statement becomes:

select location, mngr_no from department where location between ' ' and 'zzzzzzzzzz' and coalesce(mngr_no,118) between 118 and 118;

If the user supplies only 'Singapore' then the statement becomes:

select location, mngr_no from department where location between 'Singapore' and 'Singapore' and coalesce(mngr_no,-1) between -1 and 9999;

As with any programming, you need to check the extreme cases carefully - also check it runs fast enough in your particular situation.

HTH
John

> -------Original Message-------
> From: PenWin <penwin@...>
> Subject: [firebird-support] Variable query in procedure
> Sent: 01 Dec '06 18:13
>
> 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
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> 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
>
>
>
>