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
>
>
>
>
>
>
>
>
>