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.

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

);

To use:

SELECT * FROM OPS_STOP_REC WHERE Is_Manifested_CM = 1;

Sean

P.S. The "_CM" suffix to the column/field name is just my way of indicating that the column is COMPUTED.