Unfortunately the filter cannot be applied at program level as the SP
will be calling lower levelled sp's within them, so the main sp which I
would pass the param into would need to also call the other stored
procs, and pass that param into those to so that the resulting answerset
from the main SP would combine others answersets which have been run
with the same identical where conditions.



Thanks



Martin

________________________________

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Jason Dodson
Sent: 21 April 2005 15:30
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] SP Parameter as sql statement ?



You won't be able to go about this the way you are trying. If you are
doing it for speed, you can make some evil hackery by passing in the
param name (or some sort of identifier) and having a big fat CASE
statement.

If you simply want to use the where to filter the result set, put the
where on the actual select statement using the stored procedure:

Select Monkey, Ape
From Test1("blah")
Where Filteringcriteriagoeshere = true

Martin Dew wrote:

> Hi,
>
>
>
> I have a need to pass an additional Where statement element on the fly
> into a stored procedure, this wherestatement1 parameter will need to
> contain p.area in ('AAA','BBB','CCC') ;
>
>
>
> CREATE PROCEDURE TEST1 (
>
> DATEFROM DATE,
>
> DATETO DATE,
>
> WHERESTATEMENT1 VARCHAR(250)
>
> ) RETURNS (
>
> TOTAL INTEGER
>
> ) AS
>
> BEGIN
>
> FOR
>
> SELECT
>
> count(l.urn)
>
> FROM
>
> log l
>
> left outer join patient p on (l.urn = p.log_urn)
>
>
>
> WHERE
>
> (l.taken_at >= :DATEFROM
>
> and
>
> l.taken_at <= :DATETO)
>
> and
>
> (:Wherestatment)
>
> INTO
>
> :TOTAL
>
> DO BEGIN
>
> SUSPEND;
>
> END
>
> END
>
>
>
> This is not allowed by the compiler, so my questions is how would you
> approach it ? I need to run the stored procedures for infitate differe
> configurations of this optional where statement, so do not want to

have

> to write literally thousands of alternative SP's.
>
>
>
> Any help on approach would be much appreciated.
>
>
>
> Martin
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
>
>

________________________________

Yahoo! Groups Links

* To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

* To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com
<mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe

>

* Your use of Yahoo! Groups is subject to the Yahoo! Terms of
Service <http://docs.yahoo.com/info/terms/> .



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