Dynamic WHERE for a CICS search screen - 7 optional fields

CICS screen used to search on policy issue date only. Now users can fill any combo of 7 fields. Spec says "any combination."

Building one giant static SQL with ORs is ugly and the DBA already hates it for index reasons. Dynamic SQL string in COBOL feels error-prone but maybe right.

What have you shipped for this pattern without creating 2^n programs?

Priority rules first. If policy number is entered, ignore the fuzzy fields. That alone kills most combinations.

For the rest we build the WHERE in a VARCHAR and PREPARE. Validate every column name against a whitelist so you do not concatenate junk from the screen.

Watch OR-heavy predicates. They often go stage 2. Sometimes BETWEEN with host variables set to full range when a filter is unused indexes better than CASE tricks in the WHERE.

Went with priority + dynamic SQL for the leftover combos. EXPLAIN looks sane on the common paths. Thanks.