---
title: Search providers
summary: Runs a multi-criteria, dynamically assembled Oracle query across provider location, address, tax, identity, service, and MCO data.
status: draft
owner: SAIF
last_reviewed: 2026-07-30
library: SAIF.Business.Provider
reference_type: package
operation_id: OP-PRV-02
trigger: Caller populates clsSearchCriteria and calls searchForProvider()
---

# Search providers

`clsProviderSearch` builds a conditional SQL statement one clause at a time based on which search fields the caller populated, executes it through the generic `SqlHelper.ResultSetQuery` helper (not the BOF CRUD data portal), and caps the result set at 300 rows.

| Field | Value |
| --- | --- |
| **Operation ID** | OP-PRV-02 |
| **Trigger** | Caller populates `clsSearchCriteria` (nested class) and calls `clsProviderSearch.searchForProvider()` |
| **Inputs** | `ProviderName`, `TaxId`, `OtherId`, `ProviderType`, `ServiceType`, `MCOName`, `Street`, `City`, `State`, `ZipCode`, `AreaCode`, `PhoneNum`, `BusinessPerson`, `EligibleSearchFlag` |
| **Outputs** | `clsProviders` collection of `clsSearchResults` rows, capped at 300 |
| **Primary components** | `clsProviderSearch.searchForProvider`, `clsProviderSearch.clsSearchCriteria`, `SqlHelper.ResultSetQuery`, `clsProviderSearch.FormatSortName` |
| **Entry point** | `Business\SAIF.Business.Provider\clsProviderSearch.vb` |

## Steps

1. **Caller sets criteria.** The caller populates one or more fields on the nested `clsSearchCriteria` object; `resetSearchCriteria()` is available to clear a prior search.
2. **Business/person filter.** Based on `BusinessPerson` ("BUSINESS", "PERSON", or any other value), the base `WHERE` clause is seeded with `pl.BUSINESS_FLAG = :BusinessFlag` or left as `WHERE 1 = 1` so every subsequent condition can be appended with `AND`.
3. **Conditional joins are appended only for populated fields:**
   - `TaxId` → joins [PROVIDER_TAX_ID](../database-tables.md#provider_tax_id) as `TIN` on `TIN.TAX_ID = :TaxIDNumber` (hyphens stripped before binding).
   - `OtherId` → joins `provider_identity` as `ID` on `RTRIM(ID.IDENTITY_VALUE) = :OtherID`.
   - `ServiceType` → joins [PROVIDER_SERVICE](../database-tables.md#provider_service) as `SVC` on a trimmed service-code match.
   - `MCOName` → joins `mco_provider` as `MP` on `MP.MCO_PLAN_ID = :MCOPlan`.
   - Each present join is appended to `SQLPrimaryJoin` and applied identically to both the primary query and, when a name search is active, the registered-name union query.
4. **Name search adds a UNION.** When `ProviderName` is populated, the search also executes `PRVSearchRegName`, which joins [PROVIDER_TAX_ID](../database-tables.md#provider_tax_id) to resolve `REGISTERED_NAME` matches, and combines it with the primary result set via `UNION`.
5. **Remaining fields append straightforward `WHERE` predicates** (mirrored onto the union query when active): `ProviderType`, `Street` (prefix `LIKE`), `City`, `State`, `ZipCode`, `AreaCode`, `PhoneNum` (digits only, prefix `LIKE`), and `EligibleSearchFlag` (`pl.INELIGIBILITY_FLAG = 'N'`).
   - The ZIP-numeric requirement ([BR-002](../business-rules.md#br-002-search-zip-code-must-be-numeric)) is enforced by the caller populating a numeric ZIP before this step; `clsProviderSearch` itself binds whatever ZIP value is supplied without re-validating format.*
6. **Assemble and execute.** The primary query, its join clause, and its `WHERE` clause are appended in order; if a name search is active, the registered-name union query is appended next; the combined statement is ordered by `fullNAME ASC` and executed via `SqlHelper.ResultSetQuery.Execute(...)` against [PROVIDER_LOCATION](../database-tables.md#provider_location) joined to [PROVIDER_ADDRESS](../database-tables.md#provider_address) and `provider_type_code`.
   - Validation before this step is enforced by [BR-001](../business-rules.md#br-001-search-requires-at-least-one-criterion) (at least one criterion) and [BR-002](../business-rules.md#br-002-search-zip-code-must-be-numeric) (numeric ZIP), both raised by `clsProviderSearch` rule methods before `searchForProvider()` is expected to run.
7. **Materialize each row.** For every row returned, a `clsSearchResults` is constructed with location seq num, primary/business flags, provider type description, resolved full name, formatted phone, and physical address columns; `FormatSortName` derives a "Last, First" sort key for individuals (business names sort as-is).
8. **Stop at 300 rows.** The loop exits as soon as `m_Providers.Count > 299`, silently truncating any remaining matches — the comment in the source notes an attempted warning here "didnt work", so no truncation notice reaches the caller.*
9. **Return the collection.** `m_Providers.OrderBy(...)` is called but its result is not reassigned back to `m_Providers` in this VB code, so the final ordering exposed to the caller is the SQL `ORDER BY fullNAME ASC` order, not the in-memory `OrderBy`.*

## Process flow

```mermaid
flowchart TD
	Start([Caller populates clsSearchCriteria]) --> AtLeastOne{At least one criterion?}
	AtLeastOne -->|No| Blocked([Missing criteria<br>blocks search])
	AtLeastOne -->|Yes| ZipCheck{ZIP supplied?}
	ZipCheck -->|Yes, non-numeric| BlockedZip([Non-numeric ZIP<br>blocks search])
	ZipCheck -->|Numeric or blank| BuildJoins[Append conditional joins:<br>TIN, identity, service, MCO]
	BuildJoins --> BuildWhere[Append WHERE predicates<br>for populated fields]
	BuildWhere --> NameSearch{ProviderName supplied?}
	NameSearch -->|Yes| Union[UNION registered-name query]
	NameSearch -->|No| Execute
	Union --> Execute[Execute combined query,<br>ORDER BY fullNAME]
	Execute --> Loop[Materialize clsSearchResults rows]
	Loop --> Cap{Count > 299?}
	Cap -->|Yes| Truncate([Stop; remaining rows silently dropped])
	Cap -->|No| More{More rows?}
	More -->|Yes| Loop
	More -->|No| Done([Return clsProviders collection])
```

## Failure and edge-case behavior

- Searches with more than 300 matching rows are silently truncated; the code comments confirm an attempted warning mechanism was never wired up.*
- The final `OrderBy` call on the returned collection does not reassign its result, so callers effectively receive SQL-order results rather than a client-side re-sort.*
- The registered-name union path duplicates every join and filter condition against a second `FROM` clause; a mismatch between the two clause-building blocks would silently omit matches from one side of the union.*
- `searchForProvider()` uses `SqlHelper.ResultSetQuery` directly rather than the BOF CRUD data portal, so there is no `RowsAffected`/concurrency handling — this operation is read-only.

## Related documentation

- [Operations and processes](../operations-processes.md)
- [Business rules](../business-rules.md)
- [SQL catalog](../sql/index.md)
- [Database tables](../database-tables.md)

> **Note:** Statements marked with `*` were inferred from code structure, naming
> conventions, or contextual evidence where direct confirmation was not available
> in source artifacts.

## Sources

- `Business\SAIF.Business.Provider\clsProviderSearch.vb`