Hi Sean/Stephen!

>Stephen,
>
>> 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.
>
>Do not use a Stored Procedure, use a COMPUTED BY column.
>
>Reason == Optimizer should be able to factor the SQL of the field when deciding which indexes/best plan to use.
>SP is treated with no intelligence.

Agreed that leaving things to the optimizer is good, Sean, even though I find it mind-boggling trying to fathom how the optimizer can optimize double negation (NOT EXISTS... WHERE NOT EXISTS...), which basically is what Stephen is doing in his example.

>The syntax is pretty straight-forward:
>
>ALTER TABLE OPS_STOP_REC ADD Is_Manifested_CM COMPUTED BY (
> CASE WHEN (COALESCE( (
> SELECT 1
> FROM OPS_MANIFEST_STOPS M
> JOIN OPS_HEADER P ON P.PB_ID = O.OPS_ORDER_ID
> WHERE
> M.OSM_PKUP_DELV_ID = OPS_STOP_ID
> AND COALESCE(P.PB_STATUS, 0) <> 9)
> ), 0) = 1 THEN 1 ELSE 0 END
>
>);

This can almost work, Sean, the only problem is your use of O.OPS_ORDER_ID, since OPS_STOP_REC O is not part of your subselect. Of course, most likely you could just remove O, but I find it a bit dirty to have a JOIN that does not actually JOIN the tables in question, but simply refers to the outer table. So, I'd rather recommend using:

ALTER TABLE OPS_STOP_REC ADD Is_Manifested_CM COMPUTED BY (
CASE WHEN EXISTS(SELECT *
FROM OPS_MANIFEST_STOPS M
WHERE M.OSM_PKUP_DELV_ID = OPS_STOP_ID)
AND NOT EXISTS(SELECT * FROM OPS_HEADER P
WHERE P.PB_ID = OPS_ORDER_ID
AND P.PB_STATUS = 9) THEN 'T' ELSE 'F' END

>To use:
>
>SELECT * FROM OPS_STOP_REC WHERE Is_Manifested_CM = 1;

SELECT * FROM OPS_STOP_REC WHERE Is_Manifested_CM <> 'T';

The one thing Stephen doesn't say, is whether the selects he's using always is against OPS_STOP_REC. Your solution is not viable if he wants to use

SELECT * FROM <AnotherTable> WHERE IS_MANIFESTED(<SomeField>) <> 'T';

I think I'd rather recommend that Stephen used a view, the reason being that using COMPUTED BY columns referring to other tables/rows is not good for performance in general and if he is using SELECT * FROM OPS_STOP_REC anywhere (except in subselects like EXISTS or where IS_MANIFESTED is actually used), this is likely to get slower.

So:
CREATE VIEW V_Is_Manifested(OSM_PKUP_DELV_ID, Is_Manifested) AS
(SELECT M.OSM_PKUP_DELV_ID, iif(P.PB_ID IS NULL, 'T', 'F')
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
AND P.PB_STATUS = 9)

and

SELECT OSR.*
FROM OPS_STOP_REC OSR
LEFT JOIN V_Is_Manifested VIM on OSR.OPS_STOP_ID = VIM.OSM_PKUP_DELV_ID
WHERE VIM.IS_MANIFESTED IS DISTINCT FROM 'T'

HTH,
Set