Sorry about the unreadability of the code. The Yahoo RTF editor really mangled it.

--- In firebird-support@yahoogroups.com, "sboydlns" <sboydlns@...> wrote:
>
> I have a set of frequently used search conditions that I find myself
> repeating over and over in different places in my application. It would
> be nice to be able to encapsulate these conditions into a stored
> procedure and just reference the stored procedure in the WHERE clause of
> my SELECT statement when I need them. Trouble is, I can't figure out how
> to do it or even if it is possible.
> Consider the following procedure:
> CREATE PROCEDURE IS_MANIFESTED(stop_id bigint) RETURNS (result char(1))
> ASBEGIN IF EXISTS (SELECT M.OSM_STOP_ID
> FROM OPS_MANIFEST_STOPS M LEFT JOIN
> OPS_STOP_REC O ON O.OPS_STOP_ID = M.OSM_STOP_ID
> LEFT JOIN OPS_HEADER P ON P.PB_ID = O.OPS_ORDER_ID
> WHERE M.OSM_PKUP_DELV_ID = :stop_id AND
> COALESCE(P.PB_STATUS, 0) <> 9)) THEN BEGIN result = 'T'; END ELSE BEGIN
> result = 'F'; END SUSPEND; EXIT;END
> It would be nice to be able to use the IS_MANIFEST procedure in a select
> statement like this:
> SELECT * FROM OPS_STOP_REC WHERE IS_MANIFESTED(OPS_STOP_ID) <> 'T';
> to get all un-manifested loads.
> But this doesn't get past the syntax checker. Is there some way I can
> accomplish what I want? I suppose I could use the procedure in a JOIN
> clause but it doesn't seem as understandable or elegant.
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>