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]
